Re: Rollback locks table - why?

Jan Peters wrote:
> Yes, you are correct: I just want to issue an insertion of a row at the very beginning of an application start. So, when the table "test" is empty, the row with "runde = 0" should be inserted. If this row is already present, it should be updated.
>
This is quite common - you might find past discussions about
alternatives to the SQL:2003 MERGE statement for PostgreSQL informative.
> How do I do a "manual" ROLLBACK?
>
To roll back to a particular savepoint:

ROLLBACK TO SAVEPOINT spname;

However, for what you're trying to do another option is to just issue a
pair of statements that'll work anyway. You should probably test and see
which works better, faster, etc. Assuming there's only one row in the
table so I don't need any more specific WHERE clauses, I might try
something like:

UPDATE my_unique_table SET col = some_col_val;
INSERT INTO my_unique_table ( col ) SELECT some_col_val WHERE NOT EXISTS
(SELECT 1 FROM my_unique_table WHERE someotherconstraint = somevalue);

because one of those two is guaranteed to work whether or not there are
any rows in the table (assuming I got it all right, at least).
Presumably you're doing something like limiting app instances to one per
user, in which case you'd need additional constraints in the above (you
wouldn't be keeping just one row anymore, but one per user) and some
more indexes but the idea's basically the same.

Maybe you should tell the readers of this list a little more about what
you're trying to do and why?
--
Craig Ringer