Обсуждение: Vacuum threshold and non-serializable read-only transaction

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

ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> 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.

Surely this'd require having those transactions display exactly what
their current oldest-xmin is.  We've talked about that before, and it
seems a good idea, but it requires a bit more infrastructure than is
there now --- we'd need some snapshot-management code that could keep
track of all live snapshots within each backend.

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

Too conservative is much better than too liberal, in this case
(and I'm as bleeding-heart liberal as they come ;-))
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Surely this'd require having those transactions display exactly what
> their current oldest-xmin is.  We've talked about that before, and it
> seems a good idea, but it requires a bit more infrastructure than is
> there now --- we'd need some snapshot-management code that could keep
> track of all live snapshots within each backend.

I see. I'll need to avoid long transactions during heavily updates.
The additonal management seems to be good, but not so easy
because we should not lead lock contentions at the same time.

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