PG12的VACUUM命令相对于之前版本，新增了SKIP_LOCKED选项。在拥有SKIP_LOCKED选项时，vacuum命令会跳过被lock住的table，并且vacuum命令会被看做是成功执行。之前的版本，vacuum命令遇到了lock住的table时，vacuum命令会一直处于等待状态。
PostgreSQL 12.1
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
在第一个session中执行lock table操作：
postgres=# begin;
BEGIN
postgres=# lock table ysla in exclusive mode;
LOCK TABLE
然后在第二个session中进行vacuum测试：
postgres=# vacuum ysla;
^CCancel request sent
ERROR: canceling statement due to user request
postgres=# vacuum (skip_locked) ysla;
WARNING: skipping vacuum of "ysla" --- lock not available
VACUUM
可以通过 自PostgreSQL 11 版本增加的特性捕获SQL的执行结果
postgres=# \echo :SQLSTATE
00000
//返回00000代表执行成功
Release 说明
Add psql variables to report query activity and errors (Fabien Coelho)
Specifically, the new variables are ERROR, SQLSTATE, ROW_COUNT, LAST_ERROR_MESSAGE, and LAST_ERROR_SQLSTATE.
变量解释
关于这几个变量的说明如下:
**ERROR**
true if the last SQL query failed, false if it succeeded. See also SQLSTATE.
**SQLSTATE**
The error code (see Appendix A) associated with the last SQL query is failure, or 00000 if it succeeded.
**ROW_COUNT**
The number of rows returned or affected by the last SQL query, or 0 if the query failed or did not report a row count.
**LAST_ERROR_MESSAGE**
**LAST_ERROR_SQLSTATE**
The primary error message and associated SQLSTATE code for the most recent failed query in the current psql session, or an empty string and 00000 if no error has occurred in the current session.
附带pg10、pg11对比
PostgreSQL 10.14
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
在第一个session中执行lock table操作：
postgres=# begin;
BEGIN
postgres=# lock table test_user in exclusive mode;
LOCK TABLE
然后在第二个session中进行vacuum测试：
postgres=# vacuum test_user;
^CCancel request sent
ERROR: canceling statement due to user request
postgres=# VACUUM (skip_locked) test_user;
ERROR: unrecognized VACUUM option "skip_locked"
LINE 1: VACUUM (skip_locked) test_user;
^
PostgreSQL 11.5
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
在第一个session中执行lock table操作：
postgres=# begin;
BEGIN
postgres=# lock table qq in exclusive mode;
LOCK TABLE
然后在第二个session中进行vacuum测试：
postgres=# vacuum qq;
^CCancel request sent
ERROR: canceling statement due to user request
postgres=# vacuum (skip_locked) qq;
ERROR: unrecognized VACUUM option "skip_locked"
LINE 1: vacuum (skip_locked) qq;
^
