Thread: Select for insert possible?

Hi,

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.

Or should select .. for update actually work that way? I think it's
probably too difficult to implement it tho.

What cases would it be useful for select ... for update to _not_ block
other select ... for updates when no rows exist yet?

Are there RDBMSes with a command to atomically update if stuff already
exists, and insert if it doesn't?

Would anyone else find this useful?

Cheerio,
Link.

Lincoln Yeoh <lyeoh@pop.jaring.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 :-(.

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

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

            regards, tom lane

At 12:59 PM 3/24/01 -0500, Tom Lane wrote:
>Lincoln Yeoh <lyeoh@pop.jaring.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.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> 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.

If you're holding an exclusive-mode lock then there's no need to do the
select FOR UPDATE; there can be no other lock holder.

            regards, tom lane