Re: Select for insert possible?

At 12:59 PM 3/24/01 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
>> Is it technically possible for there to be a "select for insert"? e.g.
>> other select for inserts with the same effective where clause will block
>> even if no rows are there yet.
>
>What would you define as the "same effective where clause"? Shades of
>the halting problem, I think :-(.

Yep. It's probably not practical to do correctly. :).

Would an atomic "update if there, insert if not there (or the other way
round)" command be possible/practical though? Is there any SQL standard for
such a thing? Just curious - because it seems to be a common database
scenario. Sometimes SQL just seems like it's "chipped but not quite broken".

>I'd recommend grabbing a table-level EXCLUSIVE MODE lock, which will
>allow reads to proceed but lock out other updaters.

Thanks! That sounds good enough. I'll try that.

>Alternatively, consider whether you can't rely on a unique index to
>prevent multiple processes from inserting the "same" not-there-yet row.

I'd prefer to use that as a final guarantee (against bugs/errors for
instance), but not as a first stage check- because of that implicit
rollback thing (which I strongly agree is correct behaviour) and there
might be cases where the app can't figure out which error caused the insert
to fail and doesn't supply the correct message to the user. The error
message format/info might change in later postgresql versions.

So I'll use "lock table" (this time in exclusive mode :) ), then select for
update, then insert|update and leave the unique index to trigger an error
when I forget to all that correctly, in which case it's definitely an error.

Somehow I just like doing things that way :).

Cheerio,
Link.