SEO

The following command sequence appears to lock up the database system:

BEGIN;
LOCK pg_authid;
PREPARE TRANSACTION 'foo';
\q

After that you can't connect anymore, even in single-user mode. The only way
I could find is to clear out the pg_twophase directory, but I'm not sure
whether it is safe to do that.

Should this be prevented somehow, and is there a better recovery path?

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

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

is it good to allow locks on system tables at all?
i am not so sure. have seen some disaster in the past with that. just
consider somebody placing ACCESS EXCLUSIVE LOCK on a system table. it
is basically denial of service.

best regards,

hans



On Jul 10, 2007, at 3:14 PM, Peter Eisentraut wrote:

> The following command sequence appears to lock up the database system:
>
> BEGIN;
> LOCK pg_authid;
> PREPARE TRANSACTION 'foo';
> \q
>
> After that you can't connect anymore, even in single-user mode.
> The only way
> I could find is to clear out the pg_twophase directory, but I'm not
> sure
> whether it is safe to do that.
>
> Should this be prevented somehow, and is there a better recovery path?
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings



--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

Peter Eisentraut <peter_e@gmx.net> writes:
> The following command sequence appears to lock up the database system:
> BEGIN;
> LOCK pg_authid;
> PREPARE TRANSACTION 'foo';
> \q

> After that you can't connect anymore, even in single-user mode. The
> only way I could find is to clear out the pg_twophase directory, but
> I'm not sure whether it is safe to do that.

> Should this be prevented somehow, and is there a better recovery path?

AFAICS this is just one of many ways in which a superuser can shoot
himself in the foot; I'm not eager to try to prevent it.

Right offhand, clearing pg_twophase while the system is stopped should
be safe enough.

regards, tom lane

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

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, Jul 10, 2007 at 10:41:31AM -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > The following command sequence appears to lock up the database system:
> > BEGIN;
> > LOCK pg_authid;
> > PREPARE TRANSACTION 'foo';
> > \q
>
> > After that you can't connect anymore, even in single-user mode. The
> > only way I could find is to clear out the pg_twophase directory, but
> > I'm not sure whether it is safe to do that.
>
> > Should this be prevented somehow, and is there a better recovery path?
>
> AFAICS this is just one of many ways in which a superuser can shoot
> himself in the foot; I'm not eager to try to prevent it.
>
> Right offhand, clearing pg_twophase while the system is stopped should
> be safe enough.

It might make sense then to clear the pg_twophase directory on DB
startup. Nobody would expect the locks to persist a database restart --
or am I way off?

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGlFdxBcgs9XrR2kYRAp9/AJ4s8fBkhtaxqfu0QxBhSN2lCi++zgCfRsS9
Jpjv6513ubPtfldf2fItzj0=
=KAW0
-----END PGP SIGNATURE-----


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

tomas@tuxteam.de writes:
> On Tue, Jul 10, 2007 at 10:41:31AM -0400, Tom Lane wrote:
>> Right offhand, clearing pg_twophase while the system is stopped should
>> be safe enough.

> It might make sense then to clear the pg_twophase directory on DB
> startup.

<blink> I fear you have 100% misunderstood the point. The *only*
reason for that feature is to survive DB crashes.

regards, tom lane

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wed, Jul 11, 2007 at 12:38:09AM -0400, Tom Lane wrote:
> tomas@tuxteam.de writes:
[...]
> > It might make sense then to clear the pg_twophase directory on DB
> > startup.
>
> <blink> I fear you have 100% misunderstood the point. The *only*
> reason for that feature is to survive DB crashes.

Ah -- so it is intentional that it keeps the DB from starting again.
OK, then I misunderstood. Sorry for the noise.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFGlJG5Bcgs9XrR2kYRApC9AJsF+wm9z5zJXpZ98ThuV/gn9ozpVwCfbf3L
G4OA0pu3rh/o2rOL/OvZ9bU=
=+fd6
-----END PGP SIGNATURE-----


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

http://archives.postgresql.org

tomas@tuxteam.de writes:
> On Wed, Jul 11, 2007 at 12:38:09AM -0400, Tom Lane wrote:
>> tomas@tuxteam.de writes:
> [...]
>> > It might make sense then to clear the pg_twophase directory on DB
>> > startup.
>>
>> <blink> I fear you have 100% misunderstood the point. The *only*
>> reason for that feature is to survive DB crashes.
>
> Ah -- so it is intentional that it keeps the DB from starting again.
> OK, then I misunderstood. Sorry for the noise.

I don't think that is so much "intentional" as it is an "emergent
property."

The usual point to 2PC is that once transactions are PREPAREd, they
*need* to be stored robustly enough to survive even a DB crash.

If one locks certain vital system resources, as part of that PREPAREd
transaction, that evidently causes some problems, alas...

The right resolution to this is not, a priori, evident yet.
--
(format nil "~S@~S" "cbbrowne" "linuxdatabases.info")
http://cbbrowne.com/info/linuxdistributions.html
Rules of the Evil Overlord #38. "If an enemy I have just killed has a
younger sibling or offspring anywhere, I will find them and have them
killed immediately, instead of waiting for them to grow up harboring
feelings of vengeance towards me in my old age."
<http://www.eviloverlord.com/>

On Wed, Jul 11, 2007 at 10:43:23AM -0400, Chris Browne wrote:
> The right resolution to this is not, a priori, evident yet.

_A posteriori_, though, it seems to me the right resolution is "don't
do that" ;-)

A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz

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

Chris Browne wrote:
> If one locks certain vital system resources, as part of that PREPAREd
> transaction, that evidently causes some problems, alas...
>
> The right resolution to this is not, a priori, evident yet.

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.

FWIW, deleting the files from pg_twophase is safe when the system is
shut down.

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

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

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?

A

--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz

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