Vacuum threshold and non-serializable read-only transaction

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Vacuum threshold and non-serializable read-only transaction
Date: 2008-01-28 05:05:53
Message-ID: 20080128132701.7D65.52131E4D@oss.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Does not virtual transaction IDs in 8.3 help us to shorten vacuum threshold?

I think we can remove recently dead tuples even if non-serializable read-only
transactions are still alive, because those transactions will not see older
versions of tuples.

Another strange thing is that if an open transaction does nothing except
"BEGIN", VACUUM can remove dead tuples that are deleted after the first
transaction started. However, if an transaction performed some commands
(including simple "SELECT 1"), subsequent VACUUMs cannot remove those tuples.

Is it proper behavior? I worry about too conservative estimation
in incrementing ShmemVariableCache->latestCompletedXid.

----
(1 and 2 are terminal numbers.)

[A]
1=# BEGIN;

2=# UPDATE header SET targetid = targetid + 1 WHERE id = 1;
UPDATE 1
2=# VACUUM VERBOSE header;
INFO: vacuuming "public.header"
INFO: "header": found 1 removable, 3 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet. <- Ok, removed.
There were 3 unused item pointers.

[B]
1=# BEGIN;
1=# SELECT 1; -- ** Perform a query **

2=# UPDATE header SET targetid = targetid + 1 WHERE id = 1;
UPDATE 1
2=# VACUUM VERBOSE header;
INFO: vacuuming "public.header"
INFO: "header": found 0 removable, 4 nonremovable row versions in 1 pages
DETAIL: 1 dead row versions cannot be removed yet. <- Cannot remove!
There were 2 unused item pointers.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-01-28 05:49:23 Re: Vacuum threshold and non-serializable read-only transaction
Previous Message Tom Lane 2008-01-28 04:27:15 Re: pl/pgsql Plan Invalidation and search_path