PostgreSQL DISCARD SEQUENCES

4 minute read

背景

PostgreSQL当前版本(9.3以及9.3以下版本)DISCARD没有discard sequence的功能. 即使DISCARD ALL也不会.

9.4会不会加入这个补丁, 现在还不确定, 要看committer是否能同意将之提交.

例子

digoal=# create sequence seq_test;  
CREATE SEQUENCE  
digoal=# select currval('seq_test');  
ERROR:  currval of sequence "seq_test" is not yet defined in this session  
digoal=# select nextval('seq_test');  
 nextval   
---------  
       1  
(1 row)  
digoal=# select currval('seq_test');  
 currval   
---------  
       1  
(1 row)  
digoal=# discard all;  
DISCARD ALL  

discard all后, sequence cache还在, 没有被释放掉.

digoal=# select currval('seq_test');  
 currval   
---------  
       1  
(1 row)  

在某些情况下你可能会遇到麻烦. 例如在使用连接池时, 是否一个连接给其他客户端使用前, 首先要将这个连接回到初始状态. 那么可以通过执行DISCARD ALL;来实现.

例如pgbouncer的server_reset_query配置选项.

==== server_reset_query ====  
  
Query sent to server on connection release, before making it  
available to other clients.  At that moment no transaction is in  
progress so it should not include `ABORT` or `ROLLBACK`.  
  
A good choice for Postgres 8.2 and below is:  
  
  server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;  
  
for 8.3 and above its enough to do:  
  
  server_reset_query = DISCARD ALL;  
  
When transaction pooling is used, the `server_reset_query` should be empty,  
as clients should not use any session features.  
  
Default: DISCARD ALL  

当我们在使用这个连接池时, 如果用到了序列, 那么这个连接在释放后, 其他会话如果连上来使用到了刚才释放的连接, 那么这个新的会话将可以直接获取已经使用过的序列值. 测试如下 :

postgres@db-172-16-3-39-> cat config1999.ini   
  
使用事务模式, 每次server connection在被释放前都执行DISCARD ALL;  
[databases]  
digoal = host=172.16.3.33 dbname=digoal port=1919 pool_size=8  
[pgbouncer]  
pool_mode = transaction  
listen_port = 1999  
unix_socket_dir = /opt/pgbouncer/etc  
listen_addr = *  
auth_type = md5  
auth_file = /opt/pgbouncer/etc/users1999.txt  
logfile = /dev/null  
pidfile = /opt/pgbouncer/etc/pgbouncer1999.pid  
max_client_conn = 10000  
reserve_pool_timeout = 0  
server_reset_query = DISCARD ALL;  
admin_users = pgbouncer_admin  
stats_users = pgbouncer_guest  
ignore_startup_parameters = extra_float_digits  
  
  
postgres@db-172-16-3-39-> cat users1999.txt   
"digoal" "md5462f71c79368ccf422f8a773ef40074d"  
"pgbouncer_admin" "pgbouncer_admin3321"  
"postgres" "postgres"  

启动pgbouncer

/opt/pgbouncer/bin/pgbouncer -d /opt/pgbouncer/etc/config1999.ini  

SESSION A :

postgres@db-172-16-3-39-> psql -h 127.0.0.1 -p 1999 -U postgres -d digoal  
digoal=# begin;  
BEGIN  
digoal=# prepare a(int) as select $1;  
PREPARE  
digoal=# execute a(1);  
 ?column?   
----------  
        1  
(1 row)  
digoal=# end;  
COMMIT  
-- 因为事务模式, 连接会被回收, 执行了DISCARD ALL; 所以接下来a(int)不可用.  
digoal=# execute a(1);  
ERROR:  prepared statement "a" does not exist  

同时我们也看到sequence cache未释放.

digoal=# create sequence seq_test;  
CREATE SEQUENCE  
digoal=# select currval('seq_test');  
ERROR:  currval of sequence "seq_test" is not yet defined in this session  
digoal=# select nextval('seq_test');  
 nextval   
---------  
       1  
(1 row)  
digoal=# select currval('seq_test');  
 currval   
---------  
       1  
(1 row)  
digoal=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
           1633  
(1 row)  

SESSION B :

postgres@db-172-16-3-39-> psql -h 127.0.0.1 -p 1999 -U postgres -d digoal  
Password for user postgres:   
psql (9.1.3, server 9.2.4)  
WARNING: psql version 9.1, server version 9.2.  
         Some psql features might not work.  
Type "help" for help.  

会话B在连接池中拿到了同一个server connection.

digoal=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
           1633  
(1 row)  

所以会话B能看到这个sequence cache.

digoal=# select currval('seq_test');  
 currval   
---------  
       1  
(1 row)  

打补丁

[root@db-172-16-3-33 postgresql-9.2.4]# wget http://www.postgresql.org/message-id/attachment/28820/discard_sequences.patch  
--2013-08-01 13:13:26--  http://www.postgresql.org/message-id/attachment/28820/discard_sequences.patch  
Resolving www.postgresql.org... 217.196.149.50, 87.238.57.232, 98.129.198.126, ...  
Connecting to www.postgresql.org|217.196.149.50|:80... connected.  
HTTP request sent, awaiting response... 200 OK  
Length: 4117 (4.0K) [application/octet-stream]  
Saving to: `discard_sequences.patch'  
  
100%[==========================================================================================>] 4,117       --.-K/s   in 0s        
  
2013-08-01 13:13:27 (46.2 MB/s) - `discard_sequences.patch' saved [4117/4117]  
  
[root@db-172-16-3-33 postgresql-9.2.4]# patch -p1 < ./discard_sequences.patch   
patching file doc/src/sgml/ref/discard.sgml  
patching file src/backend/commands/discard.c  
patching file src/backend/commands/sequence.c  
Hunk #1 succeeded at 1612 with fuzz 2 (offset 11 lines).  
patching file src/backend/parser/gram.y  
Hunk #1 succeeded at 1592 (offset -80 lines).  
patching file src/bin/psql/tab-complete.c  
Hunk #1 succeeded at 2106 (offset -268 lines).  
patching file src/include/commands/sequence.h  
patching file src/include/nodes/parsenodes.h  
Hunk #1 succeeded at 2438 (offset -56 lines).  

重新编译

gmake  
gmake install  

重启数据库

[root@db-172-16-3-33 postgresql-9.2.4]# su - pg92  
pg92@db-172-16-3-33-> pg_ctl restart -m fast  
waiting for server to shut down.... done  
server stopped  
server starting  

我们看到discard的语法变化, 新增了SEQUENCES.

pg92@db-172-16-3-33-> psql  
psql (9.2.4)  
Type "help" for help.  
postgres=# \h discard  
Command:     DISCARD  
Description: discard session state  
Syntax:  
DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP }  

再次测试时, 序列cache也可以被释放了.

postgres@db-172-16-3-39-> psql -h 127.0.0.1 -p 1999 -U postgres -d digoal  
Password for user postgres:   
psql (9.1.3, server 9.2.4)  
WARNING: psql version 9.1, server version 9.2.  
         Some psql features might not work.  
Type "help" for help.  
  
digoal=# select pg_backend_pid();  
 pg_backend_pid   
----------------  
           3639  
(1 row)  
  
digoal=# select currval('seq_test');  
ERROR:  currval of sequence "seq_test" is not yet defined in this session  
digoal=# select nextval('seq_test');  
 nextval   
---------  
       2  
(1 row)  
digoal=# select currval('seq_test');  
ERROR:  currval of sequence "seq_test" is not yet defined in this session  

参考

1. http://www.postgresql.org/message-id/flat/CAFcNs+pEOb4oteyVX_mxxtcQfKOPnK2swaOGT=Lo-4yY3kCEog@mail.gmail.com#CAFcNs+pEOb4oteyVX_mxxtcQfKOPnK2swaOGT=Lo-4yY3kCEog@mail.gmail.com

2. http://www.postgresql.org/docs/devel/static/sql-discard.html

3. 新增释放sequence cache的代码

src/backend/commands/sequence.c

+void  
+ReleaseSequenceCaches()  
+{  
+       SeqTableData *ptr = seqtab;  
+       SeqTableData *tmp = NULL;  
+  
+       while (ptr != NULL)  
+       {  
+               tmp = ptr;  
+               ptr = ptr->next;  
+               free(tmp);  
+       }  
+  
+       seqtab = NULL;  
+}  

src/backend/commands/discard.c

+#include "commands/sequence.h"  
  
+               case DISCARD_SEQUENCES:  
+                       ReleaseSequenceCaches();  
+                       break;  
+  
// 同时在DISCARD_ALL末尾添加了  
+       ReleaseSequenceCaches();  

4. 原始DISCARD的代码 :

/*-------------------------------------------------------------------------  
 *  
 * discard.c  
 *        The implementation of the DISCARD command  
 *  
 * Copyright (c) 1996-2013, PostgreSQL Global Development Group  
 *  
 *  
 * IDENTIFICATION  
 *        src/backend/commands/discard.c  
 *  
 *-------------------------------------------------------------------------  
 */  
#include "postgres.h"  
  
#include "access/xact.h"  
#include "catalog/namespace.h"  
#include "commands/async.h"  
#include "commands/discard.h"  
#include "commands/prepare.h"  
#include "utils/guc.h"  
#include "utils/portal.h"  
  
static void DiscardAll(bool isTopLevel);  
  
/*  
 * DISCARD { ALL | TEMP | PLANS }  
 */  
void  
DiscardCommand(DiscardStmt *stmt, bool isTopLevel)  
{  
        switch (stmt->target)  
        {  
                case DISCARD_ALL:  
                        DiscardAll(isTopLevel);  
                        break;  
  
                case DISCARD_PLANS:  
                        ResetPlanCache();  
                        break;  
  
                case DISCARD_TEMP:  
                        ResetTempTableNamespace();  
                        break;  
  
                default:  
                        elog(ERROR, "unrecognized DISCARD target: %d", stmt->target);  
        }  
}  
  
static void  
DiscardAll(bool isTopLevel)  
{  
        /*  
         * Disallow DISCARD ALL in a transaction block. This is arguably  
         * inconsistent (we don't make a similar check in the command sequence  
         * that DISCARD ALL is equivalent to), but the idea is to catch mistakes:  
         * DISCARD ALL inside a transaction block would leave the transaction  
         * still uncommitted.  
         */  
        PreventTransactionChain(isTopLevel, "DISCARD ALL");  
  
        /* Closing portals might run user-defined code, so do that first. */  
        PortalHashTableDeleteAll();  
        SetPGVariable("session_authorization", NIL, false);  
        ResetAllOptions();  
        DropAllPreparedStatements();  
        Async_UnlistenAll();  
        LockReleaseAll(USER_LOCKMETHOD, true);  
        ResetPlanCache();  
        ResetTempTableNamespace();  
}  

Flag Counter

digoal’s 大量PostgreSQL文章入口