Hi all,
When I was playing with VACUUM, I found that if I have prepared xacts
on the database A, I can't vacuum full on the database B.
Scenario:
1.) Prepare some transaction on "testdb" database.
2.) Create database "pgbench".
3.) Run "pgbench -i" to load pgbench data on "pgbench" database
4.) Delete all records from "accounts" table.
5.) Do VACUUM FULL on "pgbench" database.
6.) "accounts" table will not be shrinked.
7.) Rollback the prepared xacts on "testdb" database.
8.) Do VACUUM FULL on "pgbench" database.
9.) "accounts" table is shrinked.
For more details, please see the attached file.
According to my investigation, when the transaction is prepared,
PROC->xmin always set from the prepared transaction id,
even if it is another database.
So vacuum can't collect the deleted row between current xid and
prepared transaction's xid, and detect them as "nonremovable rows".
I found this on 8.1.0 and current cvs.
I think the prepared xacts on any database mustn't affect to another database.
Is this bug or spec?
Any comments?
Thanks.
--
NAGAYASU Satoshi <[EMAIL PROTECTED]>
[EMAIL PROTECTED]:~% createdb testdb
CREATE DATABASE
[EMAIL PROTECTED]:~% createdb pgbench
CREATE DATABASE
[EMAIL PROTECTED]:~% psql testdb
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testdb=# create table t1 ( uid integer primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
NOTICE: GetOldestXmin: MyDatabaseId=45468, allDbs=0
NOTICE: GetOldestXmin: pid=2742, xid=11866, xmin=11866, dboid=45468
CREATE TABLE
testdb=# begin;
BEGIN
testdb=# insert into t1 values ( 1 );
INSERT 0 1
testdb=# prepare transaction 'hoge';
PREPARE TRANSACTION
testdb=# \q
[EMAIL PROTECTED]:~% pgbench -i pgbench
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"branches_pkey" for table "branches"
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2764, xid=11889, xmin=11867, dboid=45469
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"tellers_pkey" for table "tellers"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"accounts_pkey" for table "accounts"
vacuum...done.
[EMAIL PROTECTED]:~% psql testdb
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testdb=# select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+------+-------------------------------+-------+----------
11867 | hoge | 2006-02-19 18:46:44.472652+09 | snaga | testdb
(1 row)
testdb=# \connect pgbench
You are now connected to database "pgbench".
pgbench=# SELECT count(*) FROM accounts;
count
--------
100000
(1 row)
pgbench=# select pg_relation_size('accounts');
pg_relation_size
------------------
13434880
(1 row)
pgbench=# delete from accounts;
DELETE 100000
pgbench=# SELECT count(*) FROM accounts;
count
-------
0
(1 row)
pgbench=# select pg_relation_size('accounts');
pg_relation_size
------------------
13434880
(1 row)
pgbench=# VACUUM FULL accounts;
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2779, xid=12001, xmin=11867, dboid=45469
NOTICE: full_vacuum_rel: OldestXmin=11867, currentXid=12001
VACUUM
pgbench=# VACUUM FULL VERBOSE accounts;
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2779, xid=12004, xmin=11867, dboid=45469
NOTICE: full_vacuum_rel: OldestXmin=11867, currentXid=12004
INFO: vacuuming "public.accounts"
INFO: "accounts": found 0 removable, 100000 nonremovable row versions in 1640
pages
DETAIL: 100000 dead row versions cannot be removed yet.
Nonremovable row versions range from 128 to 128 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 202080 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 5400 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.01 sec.
INFO: index "accounts_pkey" now contains 100000 row versions in 221 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "accounts": moved 0 row versions, truncated 1640 to 1640 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
pgbench=# SELECT count(*) FROM accounts;
count
-------
0
(1 row)
pgbench=# select pg_relation_size('accounts');
pg_relation_size
------------------
13434880
(1 row)
pgbench=# \connect testdb
You are now connected to database "testdb".
testdb=# rollback prepared 'hoge';
ROLLBACK PREPARED
testdb=# \connect pgbench
You are now connected to database "pgbench".
pgbench=# VACUUM FULL VERBOSE accounts;
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=2815, xid=12012, xmin=12012, dboid=45469
NOTICE: full_vacuum_rel: OldestXmin=12012, currentXid=12012
INFO: vacuuming "public.accounts"
INFO: "accounts": found 100000 removable, 0 nonremovable row versions in 1640
pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 13002080 bytes.
1640 pages are or will become empty, including 1640 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.02s/0.00u sec elapsed 0.02 sec.
INFO: index "accounts_pkey" now contains 0 row versions in 221 pages
DETAIL: 100000 index row versions were removed.
218 index pages have been deleted, 218 are currently reusable.
CPU 0.00s/0.06u sec elapsed 0.05 sec.
INFO: "accounts": truncated 1640 to 0 pages
VACUUM
pgbench=# select pg_relation_size('accounts');
pg_relation_size
------------------
0
(1 row)
pgbench=# \q
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
