The following bug has been logged on the website:
Bug reference: 10155
Logged by: digoal.zhou
Email address: digoal@126.com
PostgreSQL version: 9.3.3
Operating system: CentOS 6.4 x64
Description:
SESSION A :
digoal=# begin isolation level repeatable read;
BEGIN
digoal=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | stat_pg_stat_database | table | postgres
public | tbl_cost_align | table | postgres
public | test | table | postgres
public | ttt | table | postgres
public | tttt | table | postgres
public | ttttt | table | postgres
(6 rows)
SESSION B :
digoal=# create table t as select * from pg_class;
SELECT 306
SESSION A :
no table t in it , A cann't see the t metadata in pg_class and pg_attr and
so on.
digoal=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+----------
public | stat_pg_stat_database | table | postgres
public | tbl_cost_align | table | postgres
public | test | table | postgres
public | ttt | table | postgres
public | tttt | table | postgres
public | ttttt | table | postgres
(6 rows)
SESSION B :
but B cann't reclaim rows from table t.
why?
i think postgresql cann't reclaim tuples already exists before repeatable
read transaction start, why this case t's tuples after session a and cann't
reclaim.
digoal=# delete from t;
DELETE 306
digoal=# vacuum freeze verbose t;
INFO: vacuuming "public.t"
INFO: "t": found 0 removable, 306 nonremovable row versions in 2 out of 2
pages
DETAIL: 306 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_33578"
INFO: index "pg_toast_33578_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_33578": 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.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
when SESSION end;
session b can reclaim these dead tuple.