Обсуждение: Prepared Xacts and Vacuum question

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@snaga.org>

"Satoshi Nagayasu" <snaga@snaga.org> writes:
> 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.

A prepared xact is the same as an open xact as far as vacuum is
concerned.  It's a bad idea to sit on either open or prepared xacts
for long periods ...

> I think the prepared xacts on any database mustn't affect to another database.

Wrong, consider updates to shared catalogs.
        regards, tom lane

On 2/20/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> A prepared xact is the same as an open xact as far as vacuum is
> concerned.  It's a bad idea to sit on either open or prepared xacts
> for long periods ...

I completely understand that, however it can be occured...
Prepared xacts never die...

> > I think the prepared xacts on any database mustn't affect to another database.
>
> Wrong, consider updates to shared catalogs.

But my prepared xact did not affect to the system catalogs....

I think some messages should be shown to the DBA by the backend,
because DBA will get in trouble without any information about it.

And also it should be noted on the manual.

Thanks.
--
NAGAYASU Satoshi <snaga@snaga.org>

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@snaga.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. +