Re: Errors in transactions

On Sat, Apr 10, 2004 at 12:55:46 +0100,
Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org> wrote:
> Dear PostgresQL experts,
>
> I thought I understood transactions until I found this problem. I'm
> sure it must be a common situation. Can someone point me in the right
> direction?
>
> I have a simple table that records which flags are set for various
> objects:
>
> create table obj_flags (
> obj_id integer,
> flag text,
> primary key (obj_id, flag)
> );
>
> In my application, I have a function that wants to set flag F for a set
> of objects. First pseudo-code looks like this:
>
> set_flag ( set<obj> s ) {
> SQL "begin;"
> foreach obj in s {
> SQL "insert into obj_flags values (" obj.id ", 'F');"
> }
> SQL "end;"
> }
>
> But the flag may already be set for some of the objects. This is fine
> as far as the application is concerned, but I get database errors
> objecting to the duplicates. Any error causes the entire transaction to
> fail.

One simple way to fix this is to always have a flag entry for an object.
Then you can use an update statement. Trying to do an insert or replace
is going to require some form of serialization (using serializable
transaction isolation or locking the table). Similar forms of this
problem have been discussed on the list previously.