Re: Moving to postgresql and some ignorant questions - Mailing list pgsql-general

Phoenix Kiula wrote on 14.08.2007 19:46:
> There are some cases where I would like to bunch queries into a
> transaction purely for speed purposes, but they're not interdependent
> for integrity. E.g.,
>
>   BEGIN TRANSACTION;
>   UPDATE1;
>   UPDATE2;
>   UPDATE3;
>   COMMIT;
>
> If UPDATE2 fails because it, say, violates a foreign key constraint,
> then so be it. I want UPDATE3 to go ahead. Is this not possible, or is
> there an option I can use to do these kind of independent-query
> transactions?

You could do this with savepoints which are a kind of sub-transaction inside a
"bigger" transaction.

e.g.:
BEGIN TRANSACTION;

SAVEPOINT sp1;
UPDATE1;
IF (failed) rollback to savepoint sp1;

SAVEPOINT sp1;
UPDATE2;
IF (failed) rollback to savepoint sp2;

COMMIT;

Details here: http://www.postgresql.org/docs/8.2/static/sql-savepoint.html

But I doubt that this would be faster that doing a transaction per update.

Thomas