PostgreSQL会话及阻塞相关常用SQL

====PostgreSQL===========
select * from pg_settings;
select version(), txid_current(), pg_backend_pid(), current_user, current_schema, current_timestamp;

-- 查询所有会话信息
select t.datname, t.usename, t.application_name, t.client_addr, t.state, t.wait_event, t.backend_xid, t.xact_start, t.query_start, t.query, t.* 
from pg_stat_activity t
where t.pid != pg_backend_pid();

    
-- 先确定当前有哪些pid被阻塞
SELECT pid,waiting,query_start,query FROM pg_stat_activity where waiting;


-- 再根据当前的PID查阻塞源资料
select *
from (
    select procpid, start, now() - start as lap, current_query
    from (
        select backendid, pg_stat_get_backend_pid(S.backendid) as procpid, pg_stat_get_backend_activity_start(S.backendid) as start, pg_stat_get_backend_activity(S.backendid) as current_query
        from (select pg_stat_get_backend_idset() as backendid) as S 
      ) as S
    where current_query <> '<IDLE>'
    order by lap desc
  ) b
where b.procpid =(
            select pid
            from (
                select a.locktype, a.transactionid, a.virtualtransaction, b.pid, a.mode, a.granted
                from pg_locks a, pg_locks b
                where a.transactionid = b.transactionid
                    and a.transactionid is not null
                    and a.granted = 'f'
              ) b
            where b.pid != 23512
        );
  
经确认后, 可以用select pg_terminate_backend(23495)来粗暴杀死这个阻塞源.
  
  


-- 统计各数据库占用的磁盘大小
SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20;
    
    

-- 查出所有表按大小排序并分离data与index
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;


-- 数据库表总行数
SELECT t.reltuples, pn.nspname, t.*
FROM pg_class t 
    join pg_catalog.pg_namespace pn  on t.relnamespace = pn."oid" 
WHERE t.relkind = 'r' and pn.nspname = 'gsc2206'
order by t.reltuples desc ;

--查询所有字段类型为LOB（text）的数据表及字段名称
select
    pc.relnamespace,
    pn.nspname,
    pc.relname,
    pa.attname ,
    pt.typname,
    pa.attnum,
    pa.attisdropped,
    col_description( pa.attrelid, pa.attnum ) as attcomment
from pg_catalog.pg_class pc
    join pg_catalog.pg_attribute pa on pa.attrelid = pc."oid"
    join pg_catalog.pg_type pt on pa.atttypid = pt."oid"
    join pg_catalog.pg_namespace pn on pc.relnamespace = pn."oid"
where pt.typname = 'text'
    and pn.nspname != 'pg_catalog'
order by
    pc.relname,
    pa.attnum;

--将数据表转为json
select array_to_json(array_agg(row_to_json(t))) 
from gspuser t; 



--## 使用悲观锁  模拟事务
begin transaction;

rollback;
commit;


lock table gspauresulttkk02 in access share mode;
lock table gspauresulttkk02 in access exclusive mode;
lock table gspauresultbcc03 in row exclusive mode;


--## 查询存在锁的数据表
select pl.locktype, pl.database, pl.mode, pl.relation, pc.relname, ps.*
from pg_catalog.pg_locks pl
    join pg_catalog.pg_class pc on pl.relation = pc.oid 
    join pg_catalog.pg_stat_activity ps on pl.pid= ps.pid ;




--创建用户，需要密码
postgres=# CREATE USER tkk123 WITH PASSWORD 'tkk123';

--## 单独给用户，赋予访问数据库权限，schema权限
grant connect ON DATABASE "TestDB" to tkk123;
GRANT USAGE ON SCHEMA tkk123 TO tkk123;
ALTER SCHEMA tkk123 OWNER to tkk123;


--## 授予管理员权限
ALTER USER tkk123 with SUPERUSER ;
ALTER USER name RENAME TO new_name


--## 授予指定架构下所有数据表及序列的权限
grant all privileges on all tables in schema public to tkk123;
grant all privileges on all sequences in schema public to tkk123;

--## 设置访问路径
alter user tkk123 set search_path="$user", public;


--## 注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限

--## 赋予默认数据表的权限
alter default privileges in schema public grant all privileges on tables to tkk123;

--## 赋予默认序列的权限
alter default privileges in schema public grant all privileges on sequences to tkk123;