From: | digoal(at)126(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14357: BUG : old_snapshot_threshold no effect |
Date: | 2016-10-05 16:21:40 |
Message-ID: | 20161005162140.27238.89911@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14357
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.6.0
Operating system: CentOS 6.x x64
Description:
I set old_snapshot_threshold to 1min, but it's no effect to prevent bloat?
```
postgres=# create table test01(id int, info text);
CREATE TABLE
postgres=# create table test02(id int, info text);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into test01 values (1);
INSERT 0 1
postgres=# select now();
now
-------------------------------
2016-10-06 00:14:41.338805+08
(1 row)
```
another session :
```
postgres=# insert into test02 select generate_series(1,10000);
INSERT 0 10000
postgres=# delete from test02;
postgres=# vacuum verbose test02;
INFO: vacuuming "public.test02"
INFO: "test02": found 0 removable, 10000 nonremovable row versions in 45
out of 45 pages
DETAIL: 10000 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.
INFO: vacuuming "pg_toast.pg_toast_75813"
INFO: index "pg_toast_75813_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_75813": found 0 removable, 0 nonremovable row versions in 0
out of 0 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
long time , i see the autovacuum start, and i use vacuum command cann't
pruning the dead tuples.
postgres=# vacuum verbose test02;
INFO: vacuuming "public.test02"
INFO: "test02": found 0 removable, 20000 nonremovable row versions in 89
out of 89 pages
DETAIL: 20000 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.
INFO: vacuuming "pg_toast.pg_toast_75813"
INFO: index "pg_toast_75813_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_75813": found 0 removable, 0 nonremovable row versions in 0
out of 0 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
postgres=# select * from pg_stat_all_tables where relname ='test02';
-[ RECORD 1 ]-------+------------------------------
relid | 75813
schemaname | public
relname | test02
seq_scan | 2
seq_tup_read | 20000
idx_scan |
idx_tup_fetch |
n_tup_ins | 20000
n_tup_upd | 0
n_tup_del | 20000
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 20000
n_mod_since_analyze | 0
last_vacuum | 2016-10-06 00:18:41.510941+08
last_autovacuum | 2016-10-06 00:18:39.644973+08
last_analyze |
last_autoanalyze | 2016-10-06 00:15:49.655066+08
vacuum_count | 4
autovacuum_count | 22
analyze_count | 0
autoanalyze_count | 3
```
and session a can also visitor the pages with test02:
```
postgres=# select * from test01;
id | info
----+------
1 |
(1 row)
postgres=# select * from test02;
id | info
----+------
(0 rows)
```
why ? It's a bug?
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2016-10-05 20:12:24 | Re: BUG #14357: BUG : old_snapshot_threshold no effect |
Previous Message | Tom Lane | 2016-10-05 14:45:51 | Re: BUG #14356: "FATAL: the database system is starting up" error occurs to queries after PostgreSQL server start |