BUG #14357: BUG : old_snapshot_threshold no effect

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?

Responses

Browse pgsql-bugs by date

  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