Re: Libpq enhancement

On Tue, Jun 21, 2011 at 3:55 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> For update, it's a bit more complex - we don't have a "replace into" operator...
>
> Actually, we do. 9.1 supports data modifying CTE around which it's
> possible to rig a perfectly reasonable upsert...barring that, you
> could trivially do something similar in a hand rolled backend upsert
> function that takes a row or a set of rows (fed in as a composite
> array).

I don't believe that any of the solutions we have today are guaranteed
to behave correctly in the face of concurrent activity. Because of
the way snapshot isolation works, you can try to update an existing
record, find that there isn't one, and then fail when you go to insert
because some other backend has meanwhile inserted one that isn't
visible to your snapshot. Doing the operations in the other order is
no better.

I'm not saying this is the biggest problem in the entire world, but I
do think it's a non-imaginary problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company