PostgreSQL 彻底解决膨胀问题

最新推荐文章于 2022-02-26 08:46:54 发布
最新推荐文章于 2022-02-26 08:46:54 发布
阅读量1.1k 收藏
点赞数
文章标签： 数据库 git
原文链接：https://yq.aliyun.com/articles/164
版权 
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/
weixin_33724659
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
pg_snapshot_too_old:用于在PostgreSQL中启用过旧快照的扩展
05-19
pg_snapshot_too_old 用于在PostgreSQL中启用Snapshot Too Old错误的扩展。 概要 安装扩展 ./configure make sudo make install 加载扩展 CREATE EXTENSION pg_snapshot_too_old; 配置扩展 全局配置 更新/etc/postgresql/9.4/main/postgresql.conf文件并添加以下内容： pg_snapshot_too_old_enabled = true pg_snapshot_too_old_transation_limit = 1000 会话本地配置 SET pg_snapshot_too_old_enabled = true; SET pg_snapshot_too_old_transation_limit = 1000 ; 描述 此扩展名将启用“
fetch next from的使用
zhang7583859的专栏
03-22 6995
<br />原表结构:<br />HR_NewsPaper<br />------------------<br />id<br />CompanyName<br />CompanyInfo<br />PositionName<br />PositionNumber<br />Requirement<br />Note<br /><br />为了排除冗余数据,将此表分为两表,表Company用于存储公司相关信息,表HRNewsPaper用于存储招聘信息. <br />HRNewsPaper表:<br />-
FETCH NEXT的使用
热门推荐
祝紫山（大可山人）博客[GDI+,WPF, .Net图形图像]
11-12 1万+
原表结构:HR_NewsPaper------------------idCompanyNameCompanyInfoPositionNamePositionNumberRequirementNote为了排除冗余数据,将此表分为两表,表Company用于存储公司相关信息,表HRNewsPaper用于存储招聘信息. HRNewsPaper表:------------------idCompanyId
第四十三章 SQL命令 FETCH
yaoxin521123的博客
10-13 2206
文章目录 第四十三章 SQL命令 FETCH大纲参数描述%ROWIDFETCH for UPDATE or DELETE示例 第四十三章 SQL命令 FETCH 重新定位游标，并从中检索数据。 大纲 FETCH cursor-name [INTO host-variable-list ] 参数 cursor-name - 当前打开的游标的名称。 游标名称是在DECLARE命令中指定的。 游标名称区分大小写。 INTO host-variable-list - 可选—将取操作列中的数据放入局部变量中。
CodeSmith8.0.1连接PostgreSQL报错问题解决
10-11
本文将详述CodeSmith 8.0.1版本在尝试连接到PostgreSQL数据库时遇到的问题，以及如何通过添加必要的DLL文件和理解连接字符串来解决这个问题。 首先，CodeSmith是一款强大的代码生成器，它允许开发者通过模板语言...
Arcgis连接 postgresql sde 时遇到的问题以及解决办法
12-16
在使用ArcGIS与PostgreSQL SDE进行数据交互时，可能会遇到一些技术问题。本文将针对这些问题提供解决方案，并介绍相关的DLL文件及其作用。 首先，PostgreSQL是一个开源的关系型数据库管理系统，而SDE（Spatial ...
富士通PostgreSQL解决方案概述.pptx
10-14
富士通PostgreSQL解决方案是基于开源数据库管理系统PostgreSQL构建的一套全面的企业级解决方案。富士通作为日本领先的IT服务供应商，结合其在硬件、软件和服务领域的专业知识，为全球客户提供高效、安全的数据库服务...
Ubuntu中卸载Postgresql出错的解决方法
12-16
总结来说，当在Ubuntu中卸载PostgreSQL时遇到错误，可以通过清除dpkg信息文件并使用`apt-get autoremove`命令来解决问题。不过，这种做法应视为一种临时解决方案，因为正常情况下，应当确保所有依赖关系得到妥善处理...
解决PostgreSQL服务启动后占用100% CPU卡死的问题
09-11
总的来说，解决PostgreSQL服务启动后占用100% CPU的问题，需要从多个角度进行分析，包括但不限于查询优化、后台进程管理、并发控制、硬件资源检查以及系统级的网络配置。通过深入分析和适当的调整，可以有效降低CPU...
PostgreSQL IDE pgadmin , edb postgres enterprise manager 查询慢的问题分析
weixin_34250434的博客
04-21 258
PostgreSQL 的GUI客户端比较多，有开源的，也有商业的。 用得比较多的可能是PgAdmin了，有些人可能会用EDB的PEM。 但实际上这两个GUI都有一个小问题，在返回较大的结果集时，会非常的慢。 例如 ： 数据库端创建一个表，插入约30MB数据。 postgres=> create table test (like pg_clas...
PostgreSQL “表膨胀“ 的救世主
style_boy的博客
04-30 1247
近期，生产环境出现不同库下的同一表名（小表-几十条数据）的占用size已达近G,频繁修改，删除记录，但是空间一直未释放，是何原因？ 原因就在于vacuum，而vacuum怎么存储，清理数据的可参考官方文档进行查看。 PG vacuum官方文档 https://www.postgresql.org/docs/current/routine-vacuuming.html 操作数据时，PostgreSQL会为每一个客户端提供单独的快照。客户端对快照中的数据进行更新和删除，一些数据最终变成过期数据，但它们仍存在
PostgreSQL VACUUM 之深入浅出 (一)
DBADaily 的博客
02-26 789
从一步一步精准触发 AUTOVACUUM， 到 VACUUM 优化实战，深入浅出，一看就懂
【PostgreSQL的 “OAR-01555“？】
小怪兽的博客
01-05 1199
一、介绍 如果大家用过ORACLE的话，可能对于如下这个报错不会陌生 A transaction fails with an ORA-01555 error. ORA-01555: snapshot too old (rollback segment too small) Cause: rollback records needed by a reader for consistent read are overwritten by other writers ORA-01555，作为O
数据库访问性能优化
逍遥游
07-15 481
数据库访问性能优化 特别说明： 1、本文只是面对数据库应用开发的程序员，不适合专业DBA，DBA在数据库性能优化方面需要了解更多的知识； 2、本文许多示例及概念是基于Oracle数据库描述，对于其它关系型数据库也可以参考，但许多观点不适合于KV数据库或内存数据库或者是基于SSD技术的数据库； 3、本文未深入数据库优化中最核心的执行计划分析技术。 读者对像： 开发人员：如果你是做数据库开发，那本文的内容非常适合，因为本文是从程序员的角度来谈数据库性能优化。 架构师：如果你已经是...
「前端」fetch的用法
Jack's Blog
03-22 544
原文链接：https://www.cnblogs.com/libin-1/p/6853677.html 在写项目的过程中遇到了需要使用fetch进行前后端联调的情况，贴上这篇博客巩固一下自己的知识空缺。 ...
PostgreSQL数据库中跨库访问解决方案
weixin_30340353的博客
01-13 600
PostgreSQL跨库访问有3种方法：Schema，dblink，postgres_fdw。 方法A：在PG上建立不同SCHEMA，将数据和存储过程分别放到不同的schema上，经过权限管理后进行访问。 方法A的示例如下： 测试1（测试postgres超级用户对不同schema下对象的访问） 查看当前数据库中的schema postgres=# \dn List of sch...
Postgresql管理系列-第四章Foreign Data Wrappers
魂醉的一亩二分地
06-12 1599
本章将介绍两个技术上有趣和实用的功能: 外部数据包装 (FDW) 和并行查询。 目前只有FDW的介绍，并行查询还在筹划中。 4.1. Foreign Data Wrappers (FDW) 2003年, 在 SQL 标准中添加了一个访问远程数据的规范, 称为 “SQL Management of External Data (SQL/MED)”。自9.1版以来, PostgreSQL一直在开发此功...
PostgreSQL14: 何时会报snapshot too old错误
魂醉的一亩二分地
05-30 1140
相信用过oracle的用户，对这个错误并不陌生，很早之前写过关于oracle的相关文章。 今天介绍一下，pg同样也一定的条件下也会报相关错误。 pg中是通过vacuum回收脏数据，也就是我们常说的dead tuple。但是有时候长事物会持有一些dead tuple很长时间，这样就不能及时做vacuum回收脏数据。这样就使得数据库中的表很容易膨胀，并占用额外的存储空间。从而还会导致相关SQL操作性能下降。所以为了避免以上结果，从9.6引入了old_snapshot_threshold参数。 当old_snap
PostgreSQL 数据库表膨胀率
最新发布
05-30
PostgreSQL数据库表膨胀率指的是表中存在的无效行（即已经被删除但是还占用空间的行）所占据的比例。这些无效行会导致表的大小不断膨胀，占用过多的磁盘空间，影响数据库的性能。一般来说，表膨胀率超过20%时，就需要考虑进行表的优化了。可以通过以下 SQL 语句来查询表的膨胀率： ``` SELECT relname, 100 * (reltuples - n_live_tup) / reltuples AS bloat_ratio FROM pg_stat_user_tables ORDER BY bloat_ratio DESC; ``` 其中，relname 表示表名，reltuples 表示表中总的行数，n_live_tup 表示表中有效行的数量。通过查询结果，可以找出膨胀率较高的表，然后采取相应的优化措施，如使用 VACUUM 命令清理无效行，或者重新组织表等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值