Re: [GENERAL] Solution to UPDATE...INSERT problem - Mailing list pgsql-hackers

From Christopher Kings-Lynne
Subject Re: [GENERAL] Solution to UPDATE...INSERT problem
Date
Msg-id 00b401c2f443$420d76c0$6500a8c0@fhp.internal
Whole thread Raw
Responses Re: [GENERAL] Solution to UPDATE...INSERT problem
List pgsql-hackers
> 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



pgsql-hackers by date:

Previous
From: Hiroshi Inoue
Date:
Subject: Re: updateable cursors & visibility
Next
From: Kevin Brown
Date:
Subject: Deadlock while doing VACUUM??