Thread: Re: [GENERAL] Solution to UPDATE...INSERT problem

> AFAIK the "except" select won't see other inserts in uncommitted
> transactions. If those transactions are committed you will end up with the
> same problem. You can try it yourself, by manually doing two separate
> transactions in psql.

Yeah, I see that now.

> You either have to lock the whole table, or lock at the application layer.
> Some time back I suggested a "lock on arbitrary string" feature for
> postgresql for this and various other purposes, but that feature probably
> wouldn't scale in terms of management (it requires 100% cooperation
amongst
> all apps/clients involved).
>
> There's no "select * from table where pkey=x for insert;" which would
block
> on uncommitted inserts/updates of pkey=x and other selects for
insert/update.

How about user locks?  Isn't there something in contrib/ for that???  I
could do a userlock on the primary key, whether it existed or not?

Chris

At 05:28 PM 3/27/03 +0800, Christopher Kings-Lynne wrote:
> > There's no "select * from table where pkey=x for insert;" which would
>block
> > on uncommitted inserts/updates of pkey=x and other selects for
>insert/update.
>
>How about user locks?  Isn't there something in contrib/ for that???  I
>could do a userlock on the primary key, whether it existed or not?

Depends on your case, whether you can correctly convert your potential
primary keys into integers to be locked on.

It still requires full cooperation by all relevant apps/clients.

Actually select ... for updates also require cooperation, but it's a
standard way of doing things,  so apps that don't cooperate can be said to
be broken :).

Is there a standard for "select ... for insert"? Or lock table for insert
where pkey=x?

Regards,
Link.