PostgreSQL常用命令

添加索引

CREATE INDEX时，可以使用CONCURRENTLY参数并行创建索引，使用CONCURRENTLY参数不会锁表，创建索引过程中不会阻塞表的更新、删除、插入操作。

sky=# CREATE INDEX IF NOT EXISTS "error_record_action_log_id_idx" ON "error_record" ("action_log_id");
CREATE INDEX
sky=# CREATE INDEX IF NOT EXISTS "error_record_alert_id_idx" ON "error_record" ("alert_id");
CREATE INDEX
sky=# \d error_record
                                     Table "public.error_record"
    Column     |           Type           |                         Modifiers
---------------+--------------------------+-----------------------------------------------------------
 id            | integer                  | not null default nextval('error_record_id_seq'::regclass)
 code          | character varying(255)   | not null default ''::character varying
 message       | text                     | not null default ''::text
 details       | text                     |
 action_log_id | bigint                   |
 alert_id      | bigint                   |
 host_id       | bigint                   |
 extra_data    | text                     |
 create        | timestamp with time zone | not null
Indexes:
    "error_record_pkey" PRIMARY KEY, btree (id)
    "error_record_action_log_id_idx" btree (action_log_id)
    "error_record_alert_id_idx" btree (alert_id)

修改

sky=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
 id |   name   | volume_name |        image_id        | recycled | passive
----+----------+-------------+------------------------+----------+---------
 84 | lun_v2_1 | lun_v2_1    | rbd_data.4f5f04a0178de | f        | t
 85 | lun_v2_2 | lun_v2_2    | rbd_data.4f5f0672ac672 | f        | t
 86 | lun_v2_3 | lun_v2_3    | rbd_data.4f5f07f04723a | f        | t
(3 rows)

sky=# update volume set image_id=trim(leading 'rbd_data.' from volume.image_id) where image_id like '%rbd_data%';
UPDATE 3
sky=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
 id | name | volume_name | image_id | recycled | passive
----+------+-------------+----------+----------+---------
(0 rows)

sky=# select id, name, volume_name, image_id, recycled, passive from volume order by id desc;
 id |   name   | volume_name |   image_id    | recycled | passive
----+----------+-------------+---------------+----------+---------
 86 | lun_v2_3 | lun_v2_3    | 4f5f07f04723a | f        | t
 85 | lun_v2_2 | lun_v2_2    | 4f5f0672ac672 | f        | t
 84 | lun_v2_1 | lun_v2_1    | 4f5f04a0178de | f        | t
 36 | lun03    | lun03       | 18ac6c0f      | f        | t
(4 rows)

reset sequence

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1;
alter sequence alert_sequence_id start 1;
UPDATE foo SET id = DEFAULT;

List table triggers

select event_object_schema as table_schema,
       event_object_table as table_name,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') as event,
       action_timing as activation,
       action_condition as condition,
       action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
         table_name;

List idle transactions

sky=# select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null;
 datid | datname | pid | usesysid |  usename   | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event |        state        | backend_xid | backend_xmin |                                                                             query
-------+---------+-----+----------+------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
 16385 | demon   |  50 |    16384 | demon_user |                  | 127.0.0.1   |                 |       33558 | 2020-10-13 02:20:02.043835+00 | 2020-10-13 03:19:35.695803+00 | 2020-10-13 03:19:35.697997+00 | 2020-10-13 03:19:35.701515+00 |                 |            | idle in transaction |        2473 |              | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9
(1 row)

sky=# SELECT pid, now() - pg_stat_activity.query_start AS duration,  query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
 pid |    duration     |                                                                             query                                                                             |        state
-----+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
  50 | 00:23:27.889451 | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9 | idle in transaction
(1 row)

sky=# SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid where mode ='ExclusiveLock';

sky=# select pg_cancel_backend(50);                                                                                              pg_cancel_backend
-------------------
 t
(1 row)

sky=# select pg_terminate_backend(50);
 pg_terminate_backend
----------------------
 t
(1 row)

check replication status

On master:
select * from pg_stat_replication;

sky=# select * from pg_stat_replication ;
 pid  | usesysid |     usename      | application_name | client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+------------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 3256 |    16386 | demon_replicator | walreceiver      | 10.252.3.171 |                 |       48634 | 2020-10-27 03:13:57.134488+00 |              | streaming | 0/28664B00    | 0/28664B00     | 0/28664B00     | 0/28664B00      |             0 | async
 3276 |    16386 | demon_replicator | walreceiver      | 10.252.3.172 |                 |       44150 | 2020-10-27 03:18:37.949817+00 |              | streaming | 0/28664B00    | 0/28664B00     | 0/28664B00     | 0/28664B00      |             0 | async
(2 rows)

sky=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

On replica (streaming replication in my case):
select * from pg_stat_wal_receiver;

sky=# select * from pg_stat_wal_receiver ;
 pid |  status   | receive_start_lsn | receive_start_tli | received_lsn | received_tli |      last_msg_send_time      |     last_msg_receipt_time     | latest_end_lsn |       latest_end_time        | slot_name |                                                                           conninfo
-----+-----------+-------------------+-------------------+--------------+--------------+------------------------------+-------------------------------+----------------+------------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
  44 | streaming | 0/6000000         |                 1 | 0/286B4320   |            1 | 2020-11-02 11:32:49.16744+00 | 2020-11-02 11:32:16.011279+00 | 0/286B4320     | 2020-11-02 11:32:49.16744+00 |           | user=demon_replicator password=******** dbname=replication host=10.252.3.170 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1
(1 row)

sky=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

sky=# set timezone=8;
SET
sky=# show timezone;
 TimeZone
----------
 <+08>-08
(1 row)

sky=# select pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp();
 pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp
-------------------------------+------------------------------+-------------------------------
 0/2BD61730                    | 0/2BD61730                   | 2021-01-20 19:32:42.419382+08
(1 row)

Restore data from pg_dump

sky=# create database sky;
CREATE DATABASE

(ENV) [root@ceph-2 ~]# docker exec -i -u postgres sds-postgres pg_restore -d sky < ~/20201031115700
(ENV) [root@ceph-2 ~]# docker exec -it -u postgres sds-postgres psql sky
psql (9.6.6)
Type "help" for help.

sky=# select count(*) from volume;
 count
-------
  1402
(1 row)

查看xlog/WAL信息

pg_current_xlog_insert_location指写入wal buffer的位置。
pg_current_xlog_location返回已经write到wal文件的位置。

master:

sky=# select proname from pg_proc where proname like 'pg_%_location';
             proname
---------------------------------
 pg_current_xlog_flush_location
 pg_current_xlog_insert_location
 pg_current_xlog_location
 pg_last_xlog_receive_location
 pg_last_xlog_replay_location
 pg_tablespace_location
(6 rows)

sky=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/3C44F120
(1 row)

sky=# select pg_current_xlog_insert_location();
 pg_current_xlog_insert_location
---------------------------------
 0/3C44F120
(1 row)

sky=# select pg_xlogfile_name_offset('0/3C44F120');
      pg_xlogfile_name_offset
------------------------------------
 (00000001000000000000003C,4518176)
(1 row)

sky=# select pid, usename, client_addr, state, pg_xlog_location_diff( pg_current_xlog_location(), write_location) write_delay, pg_xlog_location_diff( pg_current_xlog_location(),  flush_location) flush_delay, pg_xlog_location_diff( pg_current_xlog_location(), replay_location) replay_delay from  pg_stat_replication ;
 pid  |     usename      | client_addr |   state   | write_delay | flush_delay | replay_delay
------+------------------+-------------+-----------+-------------+-------------+--------------
 5335 | replicator | 10.0.11.9   | streaming |           0 |           0 |           56
 8242 | replicator | 10.0.11.7   | streaming |           0 |           0 |           56

The pg_lsn data type can be used to store LSN (Log Sequence Number) data which is a pointer to a location in the XLOG. This type is a representation of XLogRecPtr and an internal system type of PostgreSQL.
Internally, an LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. The pg_lsn type supports the standard comparison operators, like = and >. Two LSNs can be subtracted using the - operator; the result is the number of bytes separating those write-ahead log positions.
replica:

sky=# select pg_current_xlog_location();
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.
demon=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location
-------------------------------
 0/3C43EB60
(1 row)

sky=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location
------------------------------
 0/3C43EB60
(1 row)

查询和设置参数

sky=# select  current_setting('max_standby_streaming_delay') as max_standby_streaming_delay, current_setting('hot_standby_feedback') as hot_standby_feedback, current_setting('wal_keep_segments') as wal_keep_segments, current_setting('shared_buffers') as shared_buffers, current_setting('commit_delay') as commit_delay, current_setting('synchronous_commit') as synchronous_commit, current_setting('max_wal_size') as max_wal_size, current_setting('wal_segment_size') as wal_segment_size,  current_setting('full_page_writes') as full_page_writes, current_setting('min_wal_size') as min_wal_size, current_setting('work_mem') as work_mem, current_setting('random_page_cost') as random_page_cost, current_setting('seq_page_cost') as seq_page_cost, current_setting('client_min_messages') as client_min_messages, current_setting('log_parser_stats') as log_parser_stats,  current_setting('log_statement_stats') as log_statement_stats, current_setting('log_planner_stats') as log_planner_stats;
 max_standby_streaming_delay | hot_standby_feedback | wal_keep_segments | shared_buffers | commit_delay | synchronous_commit | max_wal_size | wal_segment_size | full_page_writes | min_wal_size | work_mem | random_page_cost | seq_page_cost | client_min_messages | log_parser_stats | log_statement_stats | log_planner_stats
-----------------------------+----------------------+-------------------+----------------+--------------+--------------------+--------------+------------------+------------------+--------------+----------+------------------+---------------+---------------------+------------------+---------------------+-------------------
 30s                         | off                  | 64                | 128MB          | 0            | on                 | 1GB          | 16MB             | on               | 80MB         | 4MB      | 4                | 1             | notice              | off              | off                 | off
(1 row)

也可以通过如下命令查看参数：

sky=# SHOW max_connections;
 max_connections
-----------------
 1000
(1 row)
sky=# show max_locks_per_transaction;
 max_locks_per_transaction
---------------------------
 64
(1 row)

可以通过set_config(setting_name, new_value, is_local)进行设置。is_local用于控制是否只对当前事务生效。

SELECT set_config('log_statement_stats', 'off', false);

 set_config
------------
 off
(1 row)

SET LOCAL synchronous_commit TO OFF;
SET synchronous_commit TO OFF
例如：synchronous_commit参数用于控制当数据库提交事务时是否需要等待WAL日志写入磁盘后才向客户端返回成功。

In off mode, there is no waiting, so there can be a delay between when success is reported to the client and when the transaction is later guaranteed to be safe against a server crash. (The maximum delay is three times wal_writer_delay.) Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly. So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction.

查询当前数据库的缓存命中率以及事务提交率

sky=# select blks_hit::float/( blks_read + blks_hit ) as hit_ratio from pg_stat_database where datname=current_database();
     hit_ratio
-------------------
 0.999590362075571
(1 row)

sky=# select xact_commit::float/( xact_commit + xact_rollback ) as xact_ratio from pg_stat_database where datname=current_database();
    xact_ratio
-------------------
 0.994138864502206
(1 row)

索引扫描所占的比率

sky=# select sum(idx_scan)/(sum(idx_scan) +sum(seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname ='public';
     idx_scan_ratio
------------------------
 0.66835685593313328948
(1 row)

sky=# select relname, idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio from pg_stat_all_tables where schemaname ='public' order by  idx_scan_ratio asc;
                   relname                   |    idx_scan_ratio
---------------------------------------------+----------------------
 fs_arbitration_pool                         |                    0
 dp_block_backup_job                         |                    0
 crypto_key                                  |                    0
 fc_port                                     |                    0
 fs_user_group_fs_users                      |                    0
 alert_rule_resource_blacklist               |                    0
 dns_gateway_group                           |                    0

pg_stat_statements

sky=# \d pg_stat_statements
Did not find any relation named "pg_stat_statements".
sky=# create extension pg_stat_statements;
CREATE EXTENSION
sky=# \d pg_stat_statements
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers
---------------------+------------------+-----------
 userid              | oid              |
 dbid                | oid              |
 queryid             | bigint           |
 query               | text             |
 calls               | bigint           |
 total_time          | double precision |
 min_time            | double precision |
 max_time            | double precision |
 mean_time           | double precision |
 stddev_time         | double precision |
 rows                | bigint           |
 shared_blks_hit     | bigint           |
 shared_blks_read    | bigint           |
 shared_blks_dirtied | bigint           |
 shared_blks_written | bigint           |
 local_blks_hit      | bigint           |
 local_blks_read     | bigint           |
 local_blks_dirtied  | bigint           |
 local_blks_written  | bigint           |
 temp_blks_read      | bigint           |
 temp_blks_written   | bigint           |
 blk_read_time       | double precision |
 blk_write_time      | double precision |

sky=# select calls, total_time/calls as avg_time, left(query, 80) from pg_stat_statements order by 2 desc limit 10;
 calls |     avg_time     |                                       left
-------+------------------+----------------------------------------------------------------------------------
     9 | 17498.0615555556 | INSERT INTO "sync_test" (seq) SELECT g.id FROM generate_series(?, ?) AS g (id) ;
     1 |          898.476 | create extension pg_stat_statements;
     1 |          464.437 | ALTER TABLE "dp_block_async_replication_pair" ADD COLUMN "verified" BOOL NOT NUL
     1 |          310.666 | update volume set passive=?;
     1 |          211.801 |  SELECT pg_catalog.quote_ident(name)    FROM pg_catalog.pg_available_extensions
    27 | 199.369074074074 | DELETE FROM "volume_migration_job" WHERE "id" = $1
     1 |          177.588 | ALTER TABLE "host" ADD COLUMN "kvm_validation" bool NOT NULL DEFAULT FALSE;
     2 |         147.9175 | UPDATE "service" SET "pid" = $1 WHERE "id" = $2
     1 |          124.925 | select * from pg_statistic ;
     1 |          122.981 | ALTER TABLE "pool" ADD COLUMN IF NOT EXISTS "compressed" BOOL NOT NULL DEFAULT '
(10 rows)

Explain

Explain可以有如下选项：

sky=# \h explain
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

需要注意的是：使用ANALYZE选项时语句会被执行，所以在分析INSERT，DELETE， UPDATE，CREATE TABLE AS或者EXECUTE命令的查询计划时，应该使用一个事务来执行，得到真正的查询计划后对该事务进行回滚操作，避免因为使用ANALYZE选项而修改了数据。

sky=# set log_parser_stats = on;
SET
sky=# set log_planner_stats = on;
SET
sky=# set client_min_messages = log;
SET
sky=# explain (analyze on, timing on, verbose on, buffers on) select * from error_record where id=10;                                                                                                                      LOG:  PARSER STATISTICS
DETAIL:  ! system usage stats:
!       0.000058 elapsed 0.000059 user 0.000000 system sec
!       [0.261612 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  PARSE ANALYSIS STATISTICS
DETAIL:  ! system usage stats:
!       0.000076 elapsed 0.000076 user 0.000000 system sec
!       [0.261796 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
!       0.000001 elapsed 0.000002 user 0.000000 system sec
!       [0.261846 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
LOG:  PLANNER STATISTICS
DETAIL:  ! system usage stats:
!       0.000138 elapsed 0.000137 user 0.000000 system sec
!       [0.262110 user 0.059873 sys total]
!       0/0 [6264/216] filesystem blocks in/out
!       0/0 [1/5630] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       0/0 [185/9] voluntary/involuntary context switches
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on public.error_record  (cost=0.00..6.29 rows=1 width=184) (actual time=0.029..0.064 rows=1 loops=1)
   Output: id, code, message, details, action_log_id, alert_id, host_id, extra_data, "create"
   Filter: (error_record.id = 10)
   Rows Removed by Filter: 107
   Buffers: shared hit=5
 Planning time: 0.192 ms
 Execution time: 0.103 ms
(7 rows)

构造测试数据

sky=# DROP TABLE IF EXISTS "sync_test";
NOTICE:  table "sync_test" does not exist, skipping
DROP TABLE
sky=# CREATE TABLE IF NOT EXISTS "sync_test" ( "id" serial NOT NULL PRIMARY KEY, "seq" bigint );
CREATE TABLE
sky=# INSERT INTO "sync_test" (seq) SELECT g.id FROM generate_series(1, 100000) AS g (id) ;
INSERT 0 100000
sky=# select count(*) from sync_test;
 count
--------
 100000
(1 row)

stop a Postgres script when it encounters an error

add following to .psqlrc is far from perfection

\set ON_ERROR_STOP on

or use psql with parameter:

psql -v ON_ERROR_STOP=1

better to use also -X parameter turning off .psqlrc file usage.
or
psql --single-transaction ...
PGOPTIONS='--client-min-messages=warning' psql -X -q -a -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb -f dump.sql

[root@ceph-3 ~]# docker exec -i -u postgres sds-postgres pg_restore --single-transaction -c -d sky -p 5432 < ~/20201031115700
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4707; 2620 21400 TRIGGER vip operation_feedback_insert demon_user
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.vip" does not exist
    Command was: DROP TRIGGER operation_feedback_insert ON public.vip;

read unix @->/var/run/docker.sock: read: connection reset by peer

查询连接数

sky=# SELECT datname, numbackends FROM pg_stat_database;
  datname  | numbackends
-----------+-------------
 postgres  |           0
 template1 |           0
 template0 |           0
 demon     |          93
(4 rows)

也可以指定database进行查询

sky=# select count(*) from pg_stat_activity where datname='demon';
count
-------
   93
(1 row)

另外也可以通过如下命令查询连接数以及保留的连接数。
参考链接: Right query to get the current number of connections in a PostgreSQL DB

sky=# select  * from (select count(*) used from pg_stat_activity) q1,  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;
 used | res_for_super | max_conn
------+---------------+----------
   95 |             3 |     1000
(1 row)

也可以根据状态进行分组：

sky=# select state, count(*) from pg_stat_activity  where pid <> pg_backend_pid() group by 1 order by 1;
 state  | count
--------+-------
 active |     3
 idle   |    90
(2 rows)

sky=# SELECT count(*), state FROM pg_stat_activity GROUP BY 2;
 count |        state
-------+---------------------
     1 | active
    29 | idle
     1 | idle in transaction
(3 rows)

参考链接：Connection handling best practice with PostgreSQL

active – Identifies currently running queries; in a sense, this is truly how many connections you may require at a time
idle – Identifies connections opened to the DB (most frameworks do this and maintain a pool of connections), that are not in use. This is the one area in which a connection pooler such as PgBouncer can help most.
idle in transaction – Identifies connections against which your app has run a BEGIN but it is now waiting somewhere in a transaction and not doing work.
idle in transaction (aborted) – Identifies connections that were idle in the transaction that have since been aborted.

超时设置

statement_timeout 用来控制语句执行时长，单位是ms
idle_in_transaction_session_timeout 用来控制事务执行时长，单位是ms。

sky=# show statement_timeout;
 statement_timeout
-------------------
 6min
(1 row)

sky=# show idle_in_transaction_session_timeout;
 idle_in_transaction_session_timeout
-------------------------------------
 1h
(1 row)

PgBouncer

max_client_conn配置项表示最多允许用户多少个连接到pgbouncer；
default_pool_size表示默认连接池中建多少个到后端数据库的连接。

支持三种连接池模式：
session
Server is released back to pool after client disconnects. Default.
transaction
Server is released back to pool after transaction finishes.
statement
Server is released back to pool after query finishes. Transactions spanning multiple statements are disallowed in this mode.

pg locks

Lock monitoring
PostgreSQL rocks, except when it blocks: Understanding locks

查询表中的列是否存在

SELECT column_name FROM information_schema.columns 
WHERE table_name='your_table' and column_name='your_column';