Andrew Sullivan wrote:
> On Wed, Jul 11, 2007 at 04:44:12PM +0100, Heikki Linnakangas wrote:
>> FWIW, deleting the files from pg_twophase is safe when the system is
>> shut down.
>
> Is it safe for the PREPAREd TRANSACTIONs? I assume not. That is, in
> Peter's presumably experimental case, it might be ok to delete the
> files, but on a production system, you'd violate the semantics of 2PC
> by doing this?

It's effectively the same as manually issuing a ROLLBACK PREPARED. It
will brake the atomicity of the global transaction, if some branches of
that global transaction in other resource managers have already been
committed.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, Jul 11, 2007 at 06:15:12PM +0100, Heikki Linnakangas wrote:
> It's effectively the same as manually issuing a ROLLBACK PREPARED. It
> will brake the atomicity of the global transaction, if some branches of
> that global transaction in other resource managers have already been
> committed.

But how do you know which file to delete? Is it keyed to the
transaction identifier or something?

A

--
Andrew Sullivan | ajs@crankycanuck.ca
However important originality may be in some fields, restraint and
adherence to procedure emerge as the more significant virtues in a
great many others. --Alain de Botton

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Heikki Linnakangas wrote:
> It's not? I agree with Tom here; this is just one of the numerous
> things you can do to screw up your database as a superuser. Why would
> you LOCK the pg_auth table, or any other system table for that
> matter, in the first place? Let alone in a distributed transaction.

Well, my test case arose from a real application scenario, not an
attempt to destroy my database system.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Peter Eisentraut wrote:
> Heikki Linnakangas wrote:
>> It's not? I agree with Tom here; this is just one of the numerous
>> things you can do to screw up your database as a superuser. Why would
>> you LOCK the pg_auth table, or any other system table for that
>> matter, in the first place? Let alone in a distributed transaction.
>
> Well, my test case arose from a real application scenario, not an
> attempt to destroy my database system.

Why does the application LOCK pg_auth?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Peter Eisentraut wrote:
>> Heikki Linnakangas wrote:
>>> It's not? I agree with Tom here; this is just one of the numerous
>>> things you can do to screw up your database as a superuser. Why would
>>> you LOCK the pg_auth table, or any other system table for that
>>> matter, in the first place? Let alone in a distributed transaction.
>>
>> Well, my test case arose from a real application scenario, not an
>> attempt to destroy my database system.

> Why does the application LOCK pg_auth?

Even if there is a reason for a lock, surely it's not necessary to use
AccessExclusiveLock. A lesser lock would synchronize whatever the heck
it's doing without locking out readers.

regards, tom lane

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

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Wed, Jul 11, 2007 at 06:15:12PM +0100, Heikki Linnakangas wrote:
>> It's effectively the same as manually issuing a ROLLBACK PREPARED.

> But how do you know which file to delete?

You don't. In extremis you could probably throw together some
inspection tool that could look though the 2PC records to find out which
file mentioned an exclusive lock on pg_authid ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Heikki Linnakangas wrote:

> Why does the application LOCK pg_auth?

It does it with NOWAIT to determine if some other connection had already
locked it (because it was modifying some roles) in order not to lock up
the program. This (or something like it, because this doesn't work,
after all) is unfortunately necessary because schema changes don't obey
ordinary snapshot rules.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Andrew Sullivan wrote:
> On Wed, Jul 11, 2007 at 06:15:12PM +0100, Heikki Linnakangas wrote:
>> It's effectively the same as manually issuing a ROLLBACK PREPARED. It
>> will brake the atomicity of the global transaction, if some branches of
>> that global transaction in other resource managers have already been
>> committed.
>
> But how do you know which file to delete? Is it keyed to the
> transaction identifier or something?

The xid is encoded in the filename. If you can't start up the database
and look at pg_locks, you can't do much other than guess.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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

On Wed, Jul 11, 2007 at 09:26:34PM +0100, Heikki Linnakangas wrote:
>
> The xid is encoded in the filename. If you can't start up the database
> and look at pg_locks, you can't do much other than guess.

So then in this sort of case, it isn't _really_ safe to delete those
files, because the commitment you made before crash when you accepted
a PREPARE TRANSACTION is going to be gone, which violates the 2PC
rules.

A

--
And

rew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Andrew Sullivan wrote:
> On Wed, Jul 11, 2007 at 09:26:34PM +0100, Heikki Linnakangas wrote:
>> The xid is encoded in the filename. If you can't start up the database
>> and look at pg_locks, you can't do much other than guess.
>
> So then in this sort of case, it isn't _really_ safe to delete those
> files, because the commitment you made before crash when you accepted
> a PREPARE TRANSACTION is going to be gone, which violates the 2PC
> rules.

Yes, though if neither you nor the transaction manager can connect, you
don't have much choice.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly