Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql prepared transactions

Postgresql prepared transactions

From
Radosław Smogura
Date:
Hi,

I do following commands

test=# BEGIN;
BEGIN
test=# insert into testxa1 values(1);
INSERT 0 1
test=# insert ddddduuuuuppppp;
ERROR:  syntax error at or near "ddddduuuuuppppp"
LINE 1: insert ddddduuuuuppppp;
               ^
test=# PREPARE TRANSACTION 'a';
ROLLBACK

Why on prepare transaction I got rollback. If I skip error, each RM will try
to do two-phase commit, but it's looks impossible to do two-phase commit.

Regards,
Radek

Re: Postgresql prepared transactions

From
Tom Lane
Date:
=?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> I do following commands

> test=# BEGIN;
> BEGIN
> test=# insert into testxa1 values(1);
> INSERT 0 1
> test=# insert ddddduuuuuppppp;
> ERROR:  syntax error at or near "ddddduuuuuppppp"
> LINE 1: insert ddddduuuuuppppp;
>                ^
> test=# PREPARE TRANSACTION 'a';
> ROLLBACK

> Why on prepare transaction I got rollback.

Because the transaction had already failed due to the error.  This is
the same as if you'd done a plain COMMIT at that point.

            regards, tom lane

Re: Postgresql prepared transactions

From
Radosław Smogura
Date:
Tom Lane <tgl@sss.pgh.pa.us> Saturday 19 February 2011 00:48:00
> =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> > I do following commands
> >
> > test=# BEGIN;
> > BEGIN
> > test=# insert into testxa1 values(1);
> > INSERT 0 1
> > test=# insert ddddduuuuuppppp;
> > ERROR:  syntax error at or near "ddddduuuuuppppp"
> > LINE 1: insert ddddduuuuuppppp;
> >
> >                ^
> >
> > test=# PREPARE TRANSACTION 'a';
> > ROLLBACK
> >
> > Why on prepare transaction I got rollback.
>
> Because the transaction had already failed due to the error.  This is
> the same as if you'd done a plain COMMIT at that point.
>
>             regards, tom lane

Sorry, my wrong. I thought I can commit or rollback. In any way next bug JDBC.

Regards

Re: Postgresql prepared transactions

From
Radosław Smogura
Date:
Hi,

In any way may I ask to add information in protocol-message-formats.html that
for command status for queries like COMMIT, ROLLBACK, PREPARE TRANSACTION
status may be ROLLBACK. It is important for XA resources to check what
actually happend. I can't find this in documentation as well.

Radek,

Radosław Smogura <rsmogura@softperience.eu> Saturday 19 February 2011 01:02:36
> Tom Lane <tgl@sss.pgh.pa.us> Saturday 19 February 2011 00:48:00
>
> > =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> > > I do following commands
> > >
> > > test=# BEGIN;
> > > BEGIN
> > > test=# insert into testxa1 values(1);
> > > INSERT 0 1
> > > test=# insert ddddduuuuuppppp;
> > > ERROR:  syntax error at or near "ddddduuuuuppppp"
> > > LINE 1: insert ddddduuuuuppppp;
> > >
> > >                ^
> > >
> > > test=# PREPARE TRANSACTION 'a';
> > > ROLLBACK
> > >
> > > Why on prepare transaction I got rollback.
> >
> > Because the transaction had already failed due to the error.  This is
> > the same as if you'd done a plain COMMIT at that point.
> >
> >             regards, tom lane
>
> Sorry, my wrong. I thought I can commit or rollback. In any way next bug
> JDBC.
>
> Regards