Thread: How to implement nested transactions

I want to implement nest transactions like

begin;
  CREATE temp table t2 (foo char(20) primary key);
  begin;
    CREATE temp table t1 (bar char(20) primary key);
    commit;
  rollback;

I'm expecting that t1 and t2 tables are not created since last rollback
rolls back its nested transaction.

However, both tables are created.

Any idea hot to force parent transaction rollback to roll back committed
nested transactions ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:
> I want to implement nest transactions like
> begin;
>   CREATE temp table t2 (foo char(20) primary key);
>   begin;
>     CREATE temp table t1 (bar char(20) primary key);
>     commit;
>   rollback;

That is not the correct syntax.  Use SAVEPOINT, then ROLLBACK TO SAVEPOINT
or RELEASE SAVEPOINT.

            regards, tom lane

> That is not the correct syntax.  Use SAVEPOINT, then ROLLBACK TO SAVEPOINT
> or RELEASE SAVEPOINT.

postgres log file:

2006-01-26 21:45:59 LOG:  statement: INSERT INTO dok  .....
2006-01-26 21:45:59 ERROR:  insert or update on table "dok" violates foreign
key constraint "dok_klient_fkey"
2006-01-26 21:45:59 DETAIL:  Key (klient)=(ESTATEINVEST) is not present in
table "klient".
2006-01-26 21:45:59 STATEMENT:  INSERT INTO dok  ........
2006-01-26 21:45:59 LOG:  statement: ROLLBACK    <-- this statement seems to
be generated automatically by Microsoft Visual FoxPro or by Postgres ODBC
driver
2006-01-26 21:45:59 LOG:  statement: ROLLBACK TO savepoint copyone
2006-01-26 21:45:59 ERROR:  ROLLBACK TO SAVEPOINT may only be used in
transaction blocks


My client application (Microsoft Visual FoxPro 9) seems to generate
automatic ROLLBACK statement in case
if one of its commands (APPEND FROM TABLE) fails
I have'nt found a way to disable this ROLLBACK

So I'm looking a way to force Postgres to ignore this ROLLBACK


Andrus.