Re: Prepared Xacts and Vacuum question

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Satoshi Nagayasu <snaga(at)snaga(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Prepared Xacts and Vacuum question
Date: 2006-03-01 00:57:26
Message-ID: 200603010057.k210vQL05026@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Because of global tables, I don't think we make any distinction between
xids of the same database and those of a different database, so the
current behavior seems correct.

---------------------------------------------------------------------------

Satoshi Nagayasu wrote:
> 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 <snaga(at)snaga(dot)org>

[ Attachment, skipping... ]

>
> ---------------------------(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

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Woodward 2006-03-01 01:03:05 Re: pg_config, pg_service.conf, postgresql.conf ....
Previous Message Bruce Momjian 2006-03-01 00:54:12 Re: Adding an ignore list to pg_restore, prototype patch #1