postgresql 死锁/进程/主键/常用sql查询

-- 查询被锁住的表
(SELECT pid FROM pg_stat_activity where query ~ 'fzqh_3_fzqhjg_1_45947')
-- 中止进程
select pg_terminate_backend((SELECT pid FROM pg_stat_activity where query ~ 'fzqh_3_fzqhjg_1_45947' LIMIT 1));

　　

查询被锁的语句

select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT, T.QUERY_START 
from PG_STAT_ACTIVITY T
where T.DATNAME = 'postgres' and T.WAIT_EVENT_TYPE = 'Lock';

　　

删除时终止语句

select pg_terminate_backend(pid),query from pg_stat_activity
where  query ~* 'tablename' and pid <> pg_backend_pid();

　　

 

ps -ef |grep postgres |wc -l  -- 进程统计相当于连接数

SELECT count(*) FROM pg_stat_activity;  -- 连接数查询

-- 根据进程号查询sql
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>' and procpid=205542
ORDER BY lap DESC;

　　

表主键查询

select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from 
pg_constraint  inner join pg_class 
on pg_constraint.conrelid = pg_class.oid 
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid 
and  pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type on pg_type.oid = pg_attribute.atttypid
where pg_class.relname = 'pre_country_house_crx_data_country_dl_partition_44' 
and pg_constraint.contype='p'

　　 

大表count查询

Select reltuples::bigint as estimate_rows from pg_class where relnamespace = 'public'::regnamespace and relname='zh_ls_4_fzztj_sum'

　空间索引创建

　索引查询

-- 查询
select * from pg_indexes where tablename = 'l_jcsj_view_11_1';
-- 删除
DROP INDEX test_index;
-- 创建空间索引
CREATE INDEX l_zhpg_nzw_jcsj_view_11_1_gis_index ON l_jcsj_view_11_1 USING GIST (geom);

　　对已存在的表添加列 和注释

alter table tableName add COLUMN columnName varchar(50);
comment on column tableName.columnName is '注释';

　　对已存在的列设置主键

ALTER TABLE tableName ADD CONSTRAINT xxx_xxx_primary_pk PRIMARY KEY (列);

　　创建自增

DROP SEQUENCE IF EXISTS {table_name}_nid_seq;
CREATE SEQUENCE {table_name}_nid_seq
START WITH 1
INCREMENT BY 1 NO MINVALUE NO MAXVALUE
CACHE 1;
alter table trans.{table_name} alter column nid set default nextval('{table_name}_nid_seq');