{12}snaga@athena:~% createdb testdb
CREATE DATABASE
{13}snaga@athena:~% createdb pgbench
CREATE DATABASE
{14}snaga@athena:~% 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
{15}snaga@athena:~% 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.
{16}snaga@athena:~% 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