Thread: comments on prepared transactions ...

i had to deal with oracle in the past couple of days (*mega sigh*)
i have seen a very interesting feature which would make sense for 
PostgreSQL users.

currently we have:

test=# \h PREPARE TRANSACTION
Command:     PREPARE TRANSACTION
Description: prepare the current transaction for two-phase commit
Syntax:
PREPARE TRANSACTION transaction_id

in oracle it is possible to comment transactions:

COMMIT COMMENT 'ORA-2PC-CRASH-TEST-n';

if we added the possibility to comment prepared transactions it would be 
far easier for DBAs to find out what to do with prepared transactions 
once something has gone wrong (at least if an application adds some 
useful data to the comment). usually when the DBA has to fix something 
it has to be done FAST - some additional info would definitely help here ...

what do people think about this?
best regards,
    hans


-- 
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

On Thu, 2005-10-06 at 19:13 +0200, Hans-Jürgen Schönig wrote:
> i had to deal with oracle in the past couple of days (*mega sigh*)
> i have seen a very interesting feature which would make sense for 
> PostgreSQL users.
> 
> currently we have:
> 
> test=# \h PREPARE TRANSACTION
> Command:     PREPARE TRANSACTION
> Description: prepare the current transaction for two-phase commit
> Syntax:
> PREPARE TRANSACTION transaction_id
> 
> in oracle it is possible to comment transactions:
> 
> COMMIT COMMENT 'ORA-2PC-CRASH-TEST-n';
> 
> if we added the possibility to comment prepared transactions it would be 
> far easier for DBAs to find out what to do with prepared transactions 
> once something has gone wrong (at least if an application adds some 
> useful data to the comment). usually when the DBA has to fix something 
> it has to be done FAST - some additional info would definitely help here ...
> 
> what do people think about this?

I like it. It allows fairly easy definition of which exact transaction
you recover to when using PITR.

For 2PC, the difficulty might be to match up the name used on one
resource manager with that used on another. So we should look at how
other resource managers implement that so a match is more easily
possible.

Best Regards, Simon Riggs

Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> in oracle it is possible to comment transactions:

> COMMIT COMMENT 'ORA-2PC-CRASH-TEST-n';

> if we added the possibility to comment prepared transactions it would be 
> far easier for DBAs to find out what to do with prepared transactions 
> once something has gone wrong (at least if an application adds some 
> useful data to the comment). usually when the DBA has to fix something 
> it has to be done FAST - some additional info would definitely help here ...

You can do that already.  All you have to do is make sure your prepared
transaction identifiers are somewhat human readable.
        regards, tom lane