Message-ID: <47E27600.6030201@postnewspapers.com.au>
Date: Thu, 20 Mar 2008 23:34:40 +0900
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Jan Peters <petersjan@gmx.at>, pgsql-sql@postgresql.org
Subject: Re: Rollback locks table - why?
References: <20080320134208.42400@gmx.net>
	<47E26D3B.4010102@postnewspapers.com.au>
	<20080320140531.33070@gmx.net>
In-Reply-To: <20080320140531.33070@gmx.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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