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;

                 ^