From: Craig Ringer <craig@postnewspapers.com.au>
To: Jan Peters <petersjan@gmx.at>
To: pgsql-sql@postgresql.org
Subject: Re: Rollback locks table - why?
Date: Thu, 20 Mar 2008 23:34:40 +0900
Message-ID: <47E27600.6030201@postnewspapers.com.au> (raw)
In-Reply-To: <20080320140531.33070@gmx.net>
References: <20080320134208.42400@gmx.net>
<47E26D3B.4010102@postnewspapers.com.au>
<20080320140531.33070@gmx.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, petersjan@gmx.at
Subject: Re: Rollback locks table - why?
In-Reply-To: <47E27600.6030201@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox