Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL snapshot too old补丁, 防止数据库膨胀

11 minute read

背景

PostgreSQL 9.6已支持snapshot too old ， 目前仅仅针对未申请事务号的事务（pg_stat_activity.backend_xid为空的事务）,这种事务会被CANCEL，而申请了事务号的事务（例如写事务）则不会触发snapshot too old。

所以即使你使用9.6，对于写事务，也不要为所欲为，因为依旧会影响垃圾回收。

PostgreSQL 9.6新增的一个patch，暂时还没有提交，这个patch主要是防止数据库中的某些long sql导致LONG snapshot导致数据库膨胀。

原理参考：

http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/

下载补丁和数据库源码（使用2015-10-13号master分支源码）

http://www.postgresql.org/message-id/flat/BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl#BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl

打补丁

[root@digoal soft_bak]# tar -zxvf postgresql-aa7f949.tar.gz  
  
[root@digoal soft_bak]# cd postgresql-aa7f949  
[root@digoal postgresql-aa7f949]# patch -p1 < ../snapshot-too-old-v3.diff   
patching file doc/src/sgml/config.sgml  
patching file src/backend/access/brin/brin.c  
patching file src/backend/access/brin/brin_revmap.c  
patching file src/backend/access/gin/ginbtree.c  
patching file src/backend/access/gin/gindatapage.c  
patching file src/backend/access/gin/ginget.c  
patching file src/backend/access/gin/gininsert.c  
patching file src/backend/access/gist/gistget.c  
patching file src/backend/access/hash/hash.c  
patching file src/backend/access/hash/hashsearch.c  
patching file src/backend/access/heap/heapam.c  
patching file src/backend/access/heap/pruneheap.c  
patching file src/backend/access/nbtree/nbtinsert.c  
patching file src/backend/access/nbtree/nbtpage.c  
patching file src/backend/access/nbtree/nbtsearch.c  
patching file src/backend/access/spgist/spgscan.c  
patching file src/backend/commands/vacuum.c  
patching file src/backend/commands/vacuumlazy.c  
patching file src/backend/storage/ipc/ipci.c  
patching file src/backend/storage/ipc/procarray.c  
patching file src/backend/storage/lmgr/lwlocknames.txt  
patching file src/backend/utils/errcodes.txt  
patching file src/backend/utils/misc/guc.c  
patching file src/backend/utils/misc/postgresql.conf.sample  
patching file src/backend/utils/time/snapmgr.c  
patching file src/include/access/brin_revmap.h  
patching file src/include/access/gin_private.h  
patching file src/include/access/nbtree.h  
patching file src/include/storage/bufmgr.h  
patching file src/include/utils/rel.h  
patching file src/include/utils/snapmgr.h  
patching file src/include/utils/snapshot.h  
patching file src/test/modules/Makefile  
patching file src/test/modules/sto/.gitignore  
patching file src/test/modules/sto/Makefile  
patching file src/test/modules/sto/t/001_snapshot_too_old.pl  
patching file src/test/modules/sto/t/002_snapshot_too_old_select.pl  
patching file src/test/perl/TestLib.pm

安装

# ./configure --prefix=/opt/pgsql9.5devel  
# gmake world -j 32  
# gmake install-world  
  
su - pg95  
vi .bash_profile  
# add by digoal  
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1931  
export PGDATA=/data01/pg_root_1931  
export LANG=en_US.utf8  
export PGHOME=/opt/pgsql9.5devel  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGDATABASE=postgres  
export PGUSER=postgres  
alias rm='rm -i'  
alias ll='ls -lh'  
unalias vi  
  
. ~/.bash_profile  
pg95@digoal-> initdb -D $PGDATA -U postgres -W -E UTF8 --locale=C

为了快速测试效果，设置1分钟的snapshot，实际应用中用户可以根据实际的需要设置。

vi $PGDATA/postgresql.conf  
old_snapshot_threshold = 1min

启动数据库

pg_ctl start

测试：

会话1

postgres=# create table test(id int, crt_time timestamp);  
CREATE TABLE  
postgres=# insert into test values (1,now()) returning *;  
 id |          crt_time            
----+----------------------------  
  1 | 2015-11-09 14:24:26.161537  
(1 row)  
INSERT 0 1

会话2，开启一个RC隔离级别的事务

postgres=# begin transaction isolation level repeatable read;  
BEGIN  
postgres=# select *,now(),clock_timestamp() from test;  
 id |          crt_time          |              now              |       clock_timestamp          
----+----------------------------+-------------------------------+------------------------------  
  1 | 2015-11-09 14:24:26.161537 | 2015-11-09 14:24:37.289911+08 | 2015-11-09 14:24:47.88468+08  
(1 row)

会话1

postgres=# update test set crt_time=now() where id=1 returning *;  
 id |          crt_time            
----+----------------------------  
  1 | 2015-11-09 14:25:04.101895  
(1 row)  
UPDATE 1

会话2

postgres=# select *,now(),clock_timestamp() from test;  
 id |          crt_time          |              now              |        clock_timestamp          
----+----------------------------+-------------------------------+-------------------------------  
  1 | 2015-11-09 14:24:26.161537 | 2015-11-09 14:24:37.289911+08 | 2015-11-09 14:25:12.821213+08  
(1 row)  
......

在发生snapshot old错误前，我们看到这个事务占领了一个snapshot id backend_xmin ， 数据库无法回收这之后产生的垃圾。

-[ RECORD 2 ]----+-------------------------------------------------------  
datid            | 13241  
datname          | postgres  
pid              | 16575  
usesysid         | 10  
usename          | postgres  
application_name | psql  
client_addr      |   
client_hostname  |   
client_port      | -1  
backend_start    | 2015-11-09 14:19:59.45462+08  
xact_start       | 2015-11-09 14:24:37.289911+08  
query_start      | 2015-11-09 14:25:12.820912+08  
state_change     | 2015-11-09 14:25:12.821263+08  
waiting          | f  
state            | idle in transaction  
backend_xid      |   
backend_xmin     | 1769  
query            | select *,now(),clock_timestamp() from test;  
  
postgres=# vacuum verbose test;  
INFO:  vacuuming "public.test"  
INFO:  "test": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages  
DETAIL:  1 dead row versions cannot be removed yet.  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins.  
0 pages are entirely empty.  
CPU 0.00s/0.00u sec elapsed 0.00 sec.  
VACUUM

当这个会话去访问1分钟前的快照时，报错。

postgres=# select *,now(),clock_timestamp() from test;  
ERROR:  snapshot too old

这个时候，可以回收之前的垃圾了，

postgres=# vacuum verbose test;  
INFO:  vacuuming "public.test"  
INFO:  "test": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet.  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins.  
0 pages are entirely empty.  
CPU 0.00s/0.00u sec elapsed 0.00 sec.  
VACUUM

因为这个事务已经释放了backend_xmin 。

-[ RECORD 2 ]----+--------------------------------------------  
datid            | 13241  
datname          | postgres  
pid              | 16575  
usesysid         | 10  
usename          | postgres  
application_name | psql  
client_addr      |   
client_hostname  |   
client_port      | -1  
backend_start    | 2015-11-09 14:19:59.45462+08  
xact_start       |   
query_start      | 2015-11-09 14:26:00.545314+08  
state_change     | 2015-11-09 14:26:00.545849+08  
waiting          | f  
state            | idle in transaction (aborted)  
backend_xid      |   
backend_xmin     |   
query            | select *,now(),clock_timestamp() from test;

再次发起请求直接报要求用户回滚事务。

postgres=# select *,now(),clock_timestamp() from test;  
ERROR:  current transaction is aborted, commands ignored until end of transaction block

年龄也可以正常的降低：

postgres=# vacuum freeze test;  
VACUUM  
postgres=# select age(relfrozenxid) from pg_class where relname='test';  
 age   
-----  
   0  
(1 row)

测试游标（貌似snapshot too old对游标不起作用）：

会话1

postgres=# insert into test select generate_series(1,100),clock_timestamp();  
INSERT 0 100

会话2

postgres=# begin;  
BEGIN  
postgres=# declare c1 scroll cursor with hold for select * from test order by id;  
DECLARE CURSOR

会话1

postgres=# update test set crt_time=now() where id=15;  
UPDATE 1  
postgres=# select * from test where id=15;  
 id |          crt_time            
----+----------------------------  
 15 | 2015-11-09 14:58:19.889967  
(1 row)

会话2，已超1分钟。

postgres=# select clock_timestamp();  
        clock_timestamp          
-------------------------------  
 2015-11-09 14:59:23.534014+08  
(1 row)

无法回收垃圾

postgres=# vacuum verbose test;  
INFO:  vacuuming "public.test"  
INFO:  "test": found 0 removable, 102 nonremovable row versions in 1 out of 1 pages  
DETAIL:  1 dead row versions cannot be removed yet.  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins.  
0 pages are entirely empty.  
CPU 0.00s/0.00u sec elapsed 0.00 sec.  
VACUUM

fetch前后数据一致。

postgres=# fetch 1 from c1;  
 id |          crt_time            
----+----------------------------  
 10 | 2015-11-09 14:40:16.318757  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |         crt_time            
----+---------------------------  
 11 | 2015-11-09 14:40:16.31876  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |          crt_time            
----+----------------------------  
 12 | 2015-11-09 14:40:16.318763  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |          crt_time            
----+----------------------------  
 13 | 2015-11-09 14:40:16.318765  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |          crt_time            
----+----------------------------  
 14 | 2015-11-09 14:40:16.318767  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |          crt_time            
----+----------------------------  
 15 | 2015-11-09 14:40:16.319126  
(1 row)  
  
postgres=# fetch 1 from c1;  
 id |         crt_time            
----+---------------------------  
 16 | 2015-11-09 14:40:16.31915  
(1 row)  
  
postgres=# fetch 100 from c1;  
 id  |          crt_time            
-----+----------------------------  
  17 | 2015-11-09 14:40:16.319153  
  18 | 2015-11-09 14:40:16.319155  
  19 | 2015-11-09 14:40:16.319158  
  20 | 2015-11-09 14:40:16.31916  
  21 | 2015-11-09 14:40:16.319163  
  22 | 2015-11-09 14:40:16.319165  
  23 | 2015-11-09 14:40:16.319168  
  24 | 2015-11-09 14:40:16.31917  
  25 | 2015-11-09 14:40:16.319173  
  26 | 2015-11-09 14:40:16.319175  
  27 | 2015-11-09 14:40:16.319178  
  28 | 2015-11-09 14:40:16.31918  
  29 | 2015-11-09 14:40:16.319183  
  30 | 2015-11-09 14:40:16.319185  
  31 | 2015-11-09 14:40:16.319188  
  32 | 2015-11-09 14:40:16.31919  
  33 | 2015-11-09 14:40:16.319193  
  34 | 2015-11-09 14:40:16.319195  
  35 | 2015-11-09 14:40:16.319198  
  36 | 2015-11-09 14:40:16.3192  
  37 | 2015-11-09 14:40:16.319203  
  38 | 2015-11-09 14:40:16.319205  
  39 | 2015-11-09 14:40:16.319208  
  40 | 2015-11-09 14:40:16.31921  
  41 | 2015-11-09 14:40:16.319213  
  42 | 2015-11-09 14:40:16.319215  
  43 | 2015-11-09 14:40:16.319218  
  44 | 2015-11-09 14:40:16.31922  
  45 | 2015-11-09 14:40:16.319222  
  46 | 2015-11-09 14:40:16.319225  
  47 | 2015-11-09 14:40:16.319227  
  48 | 2015-11-09 14:40:16.31923  
  49 | 2015-11-09 14:40:16.319232  
  50 | 2015-11-09 14:40:16.319235  
  51 | 2015-11-09 14:40:16.319237  
  52 | 2015-11-09 14:40:16.31924  
  53 | 2015-11-09 14:40:16.319242  
  54 | 2015-11-09 14:40:16.319245  
  55 | 2015-11-09 14:40:16.319247  
  56 | 2015-11-09 14:40:16.31925  
  57 | 2015-11-09 14:40:16.319252  
  58 | 2015-11-09 14:40:16.319255  
  59 | 2015-11-09 14:40:16.319257  
  60 | 2015-11-09 14:40:16.319259  
  61 | 2015-11-09 14:40:16.319262  
  62 | 2015-11-09 14:40:16.319264  
  63 | 2015-11-09 14:40:16.319267  
  64 | 2015-11-09 14:40:16.319269  
  65 | 2015-11-09 14:40:16.319272  
  66 | 2015-11-09 14:40:16.319274  
  67 | 2015-11-09 14:40:16.319277  
  68 | 2015-11-09 14:40:16.319279  
  69 | 2015-11-09 14:40:16.319282  
  70 | 2015-11-09 14:40:16.319284  
  71 | 2015-11-09 14:40:16.319287  
  72 | 2015-11-09 14:40:16.319303  
  73 | 2015-11-09 14:40:16.319306  
  74 | 2015-11-09 14:40:16.319309  
  75 | 2015-11-09 14:40:16.319311  
  76 | 2015-11-09 14:40:16.319314  
  77 | 2015-11-09 14:40:16.319316  
  78 | 2015-11-09 14:40:16.319319  
  79 | 2015-11-09 14:40:16.319321  
  80 | 2015-11-09 14:40:16.319324  
  81 | 2015-11-09 14:40:16.319326  
  82 | 2015-11-09 14:40:16.319329  
  83 | 2015-11-09 14:40:16.319331  
  84 | 2015-11-09 14:40:16.319334  
  85 | 2015-11-09 14:40:16.319336  
  86 | 2015-11-09 14:40:16.319339  
  87 | 2015-11-09 14:40:16.319341  
  88 | 2015-11-09 14:40:16.319343  
  89 | 2015-11-09 14:40:16.319346  
  90 | 2015-11-09 14:40:16.319348  
  91 | 2015-11-09 14:40:16.319351  
  92 | 2015-11-09 14:40:16.319353  
  93 | 2015-11-09 14:40:16.319356  
  94 | 2015-11-09 14:40:16.319358  
  95 | 2015-11-09 14:40:16.319361  
  96 | 2015-11-09 14:40:16.319363  
  97 | 2015-11-09 14:40:16.319366  
  98 | 2015-11-09 14:40:16.319368  
  99 | 2015-11-09 14:40:16.319371  
 100 | 2015-11-09 14:40:16.319373  
(84 rows)  
  
postgres=# fetch 100 from c1;  
 id | crt_time   
----+----------  
(0 rows)  
  
postgres=# \h fetch  
Command:     FETCH  
Description: retrieve rows from a query using a cursor  
Syntax:  
FETCH [ direction [ FROM | IN ] ] cursor_name  
  
where direction can be empty or one of:  
  
    NEXT  
    PRIOR  
    FIRST  
    LAST  
    ABSOLUTE count  
    RELATIVE count  
    count  
    ALL  
    FORWARD  
    FORWARD count  
    FORWARD ALL  
    BACKWARD  
    BACKWARD count  
    BACKWARD ALL  
  
postgres=# fetch backward 100 from c1;  
 id  |          crt_time            
-----+----------------------------  
 100 | 2015-11-09 14:40:16.319373  
  99 | 2015-11-09 14:40:16.319371  
  98 | 2015-11-09 14:40:16.319368  
  97 | 2015-11-09 14:40:16.319366  
  96 | 2015-11-09 14:40:16.319363  
  95 | 2015-11-09 14:40:16.319361  
  94 | 2015-11-09 14:40:16.319358  
  93 | 2015-11-09 14:40:16.319356  
  92 | 2015-11-09 14:40:16.319353  
  91 | 2015-11-09 14:40:16.319351  
  90 | 2015-11-09 14:40:16.319348  
  89 | 2015-11-09 14:40:16.319346  
  88 | 2015-11-09 14:40:16.319343  
  87 | 2015-11-09 14:40:16.319341  
  86 | 2015-11-09 14:40:16.319339  
  85 | 2015-11-09 14:40:16.319336  
  84 | 2015-11-09 14:40:16.319334  
  83 | 2015-11-09 14:40:16.319331  
  82 | 2015-11-09 14:40:16.319329  
  81 | 2015-11-09 14:40:16.319326  
  80 | 2015-11-09 14:40:16.319324  
  79 | 2015-11-09 14:40:16.319321  
  78 | 2015-11-09 14:40:16.319319  
  77 | 2015-11-09 14:40:16.319316  
  76 | 2015-11-09 14:40:16.319314  
  75 | 2015-11-09 14:40:16.319311  
  74 | 2015-11-09 14:40:16.319309  
  73 | 2015-11-09 14:40:16.319306  
  72 | 2015-11-09 14:40:16.319303  
  71 | 2015-11-09 14:40:16.319287  
  70 | 2015-11-09 14:40:16.319284  
  69 | 2015-11-09 14:40:16.319282  
  68 | 2015-11-09 14:40:16.319279  
  67 | 2015-11-09 14:40:16.319277  
  66 | 2015-11-09 14:40:16.319274  
  65 | 2015-11-09 14:40:16.319272  
  64 | 2015-11-09 14:40:16.319269  
  63 | 2015-11-09 14:40:16.319267  
  62 | 2015-11-09 14:40:16.319264  
  61 | 2015-11-09 14:40:16.319262  
  60 | 2015-11-09 14:40:16.319259  
  59 | 2015-11-09 14:40:16.319257  
  58 | 2015-11-09 14:40:16.319255  
  57 | 2015-11-09 14:40:16.319252  
  56 | 2015-11-09 14:40:16.31925  
  55 | 2015-11-09 14:40:16.319247  
  54 | 2015-11-09 14:40:16.319245  
  53 | 2015-11-09 14:40:16.319242  
  52 | 2015-11-09 14:40:16.31924  
  51 | 2015-11-09 14:40:16.319237  
  50 | 2015-11-09 14:40:16.319235  
  49 | 2015-11-09 14:40:16.319232  
  48 | 2015-11-09 14:40:16.31923  
  47 | 2015-11-09 14:40:16.319227  
  46 | 2015-11-09 14:40:16.319225  
  45 | 2015-11-09 14:40:16.319222  
  44 | 2015-11-09 14:40:16.31922  
  43 | 2015-11-09 14:40:16.319218  
  42 | 2015-11-09 14:40:16.319215  
  41 | 2015-11-09 14:40:16.319213  
  40 | 2015-11-09 14:40:16.31921  
  39 | 2015-11-09 14:40:16.319208  
  38 | 2015-11-09 14:40:16.319205  
  37 | 2015-11-09 14:40:16.319203  
  36 | 2015-11-09 14:40:16.3192  
  35 | 2015-11-09 14:40:16.319198  
  34 | 2015-11-09 14:40:16.319195  
  33 | 2015-11-09 14:40:16.319193  
  32 | 2015-11-09 14:40:16.31919  
  31 | 2015-11-09 14:40:16.319188  
  30 | 2015-11-09 14:40:16.319185  
  29 | 2015-11-09 14:40:16.319183  
  28 | 2015-11-09 14:40:16.31918  
  27 | 2015-11-09 14:40:16.319178  
  26 | 2015-11-09 14:40:16.319175  
  25 | 2015-11-09 14:40:16.319173  
  24 | 2015-11-09 14:40:16.31917  
  23 | 2015-11-09 14:40:16.319168  
  22 | 2015-11-09 14:40:16.319165  
  21 | 2015-11-09 14:40:16.319163  
  20 | 2015-11-09 14:40:16.31916  
  19 | 2015-11-09 14:40:16.319158  
  18 | 2015-11-09 14:40:16.319155  
  17 | 2015-11-09 14:40:16.319153  
  16 | 2015-11-09 14:40:16.31915  
  15 | 2015-11-09 14:40:16.319126  
  14 | 2015-11-09 14:40:16.318767  
  13 | 2015-11-09 14:40:16.318765  
  12 | 2015-11-09 14:40:16.318763  
  11 | 2015-11-09 14:40:16.31876  
  10 | 2015-11-09 14:40:16.318757  
   9 | 2015-11-09 14:40:16.318755  
   8 | 2015-11-09 14:40:16.318753  
   7 | 2015-11-09 14:40:16.31875  
   6 | 2015-11-09 14:40:16.318748  
   5 | 2015-11-09 14:40:16.318745  
   4 | 2015-11-09 14:40:16.318743  
   3 | 2015-11-09 14:40:16.31874  
   2 | 2015-11-09 14:40:16.318733  
   1 | 2015-11-09 14:40:16.318608  
(100 rows)  
  
postgres=# fetch backward 100 from c1;  
 id |          crt_time            
----+----------------------------  
  1 | 2015-11-09 14:25:04.101895  
(1 row)  
  
postgres=# fetch backward 100 from c1;  
 id | crt_time   
----+----------  
(0 rows)  
  
postgres=# fetch 1000 from c1;  
 id  |          crt_time            
-----+----------------------------  
   1 | 2015-11-09 14:25:04.101895  
   1 | 2015-11-09 14:40:16.318608  
   2 | 2015-11-09 14:40:16.318733  
   3 | 2015-11-09 14:40:16.31874  
   4 | 2015-11-09 14:40:16.318743  
   5 | 2015-11-09 14:40:16.318745  
   6 | 2015-11-09 14:40:16.318748  
   7 | 2015-11-09 14:40:16.31875  
   8 | 2015-11-09 14:40:16.318753  
   9 | 2015-11-09 14:40:16.318755  
  10 | 2015-11-09 14:40:16.318757  
  11 | 2015-11-09 14:40:16.31876  
  12 | 2015-11-09 14:40:16.318763  
  13 | 2015-11-09 14:40:16.318765  
  14 | 2015-11-09 14:40:16.318767  
  15 | 2015-11-09 14:40:16.319126  
  16 | 2015-11-09 14:40:16.31915  
  17 | 2015-11-09 14:40:16.319153  
  18 | 2015-11-09 14:40:16.319155  
  19 | 2015-11-09 14:40:16.319158  
  20 | 2015-11-09 14:40:16.31916  
  21 | 2015-11-09 14:40:16.319163  
  22 | 2015-11-09 14:40:16.319165  
  23 | 2015-11-09 14:40:16.319168  
  24 | 2015-11-09 14:40:16.31917  
  25 | 2015-11-09 14:40:16.319173  
  26 | 2015-11-09 14:40:16.319175  
  27 | 2015-11-09 14:40:16.319178  
  28 | 2015-11-09 14:40:16.31918  
  29 | 2015-11-09 14:40:16.319183  
  30 | 2015-11-09 14:40:16.319185  
  31 | 2015-11-09 14:40:16.319188  
  32 | 2015-11-09 14:40:16.31919  
  33 | 2015-11-09 14:40:16.319193  
  34 | 2015-11-09 14:40:16.319195  
  35 | 2015-11-09 14:40:16.319198  
  36 | 2015-11-09 14:40:16.3192  
  37 | 2015-11-09 14:40:16.319203  
  38 | 2015-11-09 14:40:16.319205  
  39 | 2015-11-09 14:40:16.319208  
  40 | 2015-11-09 14:40:16.31921  
  41 | 2015-11-09 14:40:16.319213  
  42 | 2015-11-09 14:40:16.319215  
  43 | 2015-11-09 14:40:16.319218  
  44 | 2015-11-09 14:40:16.31922  
  45 | 2015-11-09 14:40:16.319222  
  46 | 2015-11-09 14:40:16.319225  
  47 | 2015-11-09 14:40:16.319227  
  48 | 2015-11-09 14:40:16.31923  
  49 | 2015-11-09 14:40:16.319232  
  50 | 2015-11-09 14:40:16.319235  
  51 | 2015-11-09 14:40:16.319237  
  52 | 2015-11-09 14:40:16.31924  
  53 | 2015-11-09 14:40:16.319242  
  54 | 2015-11-09 14:40:16.319245  
  55 | 2015-11-09 14:40:16.319247  
  56 | 2015-11-09 14:40:16.31925  
  57 | 2015-11-09 14:40:16.319252  
  58 | 2015-11-09 14:40:16.319255  
  59 | 2015-11-09 14:40:16.319257  
  60 | 2015-11-09 14:40:16.319259  
  61 | 2015-11-09 14:40:16.319262  
  62 | 2015-11-09 14:40:16.319264  
  63 | 2015-11-09 14:40:16.319267  
  64 | 2015-11-09 14:40:16.319269  
  65 | 2015-11-09 14:40:16.319272  
  66 | 2015-11-09 14:40:16.319274  
  67 | 2015-11-09 14:40:16.319277  
  68 | 2015-11-09 14:40:16.319279  
  69 | 2015-11-09 14:40:16.319282  
  70 | 2015-11-09 14:40:16.319284  
  71 | 2015-11-09 14:40:16.319287  
  72 | 2015-11-09 14:40:16.319303  
  73 | 2015-11-09 14:40:16.319306  
  74 | 2015-11-09 14:40:16.319309  
  75 | 2015-11-09 14:40:16.319311  
  76 | 2015-11-09 14:40:16.319314  
  77 | 2015-11-09 14:40:16.319316  
  78 | 2015-11-09 14:40:16.319319  
  79 | 2015-11-09 14:40:16.319321  
  80 | 2015-11-09 14:40:16.319324  
  81 | 2015-11-09 14:40:16.319326  
  82 | 2015-11-09 14:40:16.319329  
  83 | 2015-11-09 14:40:16.319331  
  84 | 2015-11-09 14:40:16.319334  
  85 | 2015-11-09 14:40:16.319336  
  86 | 2015-11-09 14:40:16.319339  
  87 | 2015-11-09 14:40:16.319341  
  88 | 2015-11-09 14:40:16.319343  
  89 | 2015-11-09 14:40:16.319346  
  90 | 2015-11-09 14:40:16.319348  
  91 | 2015-11-09 14:40:16.319351  
  92 | 2015-11-09 14:40:16.319353  
  93 | 2015-11-09 14:40:16.319356  
  94 | 2015-11-09 14:40:16.319358  
  95 | 2015-11-09 14:40:16.319361  
  96 | 2015-11-09 14:40:16.319363  
  97 | 2015-11-09 14:40:16.319366  
  98 | 2015-11-09 14:40:16.319368  
  99 | 2015-11-09 14:40:16.319371  
 100 | 2015-11-09 14:40:16.319373  
(101 rows)

回滚后可以正常回收垃圾。

postgres=#   rollback;  
ROLLBACK  
  
postgres=# vacuum verbose test;  
INFO:  vacuuming "public.test"  
INFO:  "test": found 1 removable, 101 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet.  
There were 0 unused item pointers.  
Skipped 0 pages due to buffer pins.  
0 pages are entirely empty.  
CPU 0.00s/0.00u sec elapsed 0.00 sec.  
VACUUM

参考

1. http://www.postgresql.org/message-id/flat/BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl#BLU437-SMTP193E7367B788D2EC2B2B81DC160@phx.gbl

2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=snapshot;h=aa7f9493a02f5981c09b924323f0e7a58a32f2ed;sf=tgz

3. http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...