[PostgreSQL]常見問題

1.如何查看Table or User的存取權限

// by user
SELECT (table_schema || '.' || table_name) AS table, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE grantee='user_name'
GROUP BY (table_schema || '.' || table_name);

// by table
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants
WHERE table_name='table_name'
GROUP BY grantee;

 
 2.如何查看Database大小

SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database 
ORDER BY pg_database_size DESC;

 3.如何查看各Table的大小

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC;

SELECT nspname || '.' || relname AS "relation", T.spcname,
    pg_size_pretty(pg_relation_size(C.oid)) AS "size",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(C.oid) DESC

4.如何create 一個readonly的user

1.CREATE ROLE xxx LOGIN PASSWORD 'yyy';
2.GRANT CONNECT ON DATABASE mydb TO xxx;
3.GRANT USAGE ON SCHEMA my_schema | public TO xxx;
4.GRANT SELECT ON ALL TABLES IN SCHEMA my_schema | public TO xxx;

5.如何查到Lock的Statment

  SELECT bl.pid                 AS src_pid,
         a.usename              AS src_user,
         ka.query               AS src_stmt,
         now() - ka.query_start AS src_duration,
         kl.pid                 AS blk_pid,
         ka.usename             AS blk_user,
         a.query                AS blk_stmt,
         now() - a.query_start  AS blk_duration
  FROM  pg_locks         bl
   JOIN pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.granted;

6.pgxc如何查到table dist的Statment

 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        pg_attribute.attname attr
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = pg_attribute.attnum
 UNION
 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        'none' attr
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = 0

7.利用原有的db object來gen drop/ create stmt(alter table)

CREATE OR REPLACE FUNCTION gen_pk_stmt()
RETURNS TABLE(rtnDropStr text, rtnCreateStr text) AS $body$
DECLARE
    mviews RECORD;
    mviews2 RECORD;
    dropStr text;
    createStr text;
    rtn RECORD;
BEGIN
    FOR mviews in (SELECT nspname, relname, conname, spcname
            FROM pg_class C
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
            LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
            LEFT JOIN pg_constraint CT ON (C.oid = CT.conrelid)
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
              AND conname is not null
            ORDER BY pg_total_relation_size(C.oid) DESC) LOOP
    createStr = 'ALTER TABLE ' || mviews.nspname || '.' ||  mviews.relname || ' ADD CONSTRAINT ' || mviews.nspname || '.' || mviews.conname || ' PRIMARY KEY (';
    dropStr = 'ALTER TABLE ' || mviews.nspname || '.' ||  mviews.relname || ' DROP CONSTRAINT ' || mviews.nspname || '.' || mviews.conname;
        FOR mviews2 in (select kc.column_name
            from
                information_schema.table_constraints tc
                join information_schema.key_column_usage kc
                on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
            where tc.constraint_type = 'PRIMARY KEY'
              and tc.table_schema = mviews.nspname
              and tc.table_name = mviews.relname) LOOP
                createStr = createStr || mviews2.column_name || ',';
    END LOOP;
    createStr = rtrim(CreateStr, ',') || ')';
    if mviews.spcname is not null then
        createStr = createStr || ' USING INDEX TABLESPACE ' || mviews.spcname;
    end if;
   
    rtnDropStr = dropStr;
    rtnCreateStr = createStr;
    RETURN NEXT;
   
    END LOOP;
END
$body$ LANGUAGE PLPGSQL