PostgreSQL DISCARD SEQUENCES
背景
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();
}