Re: conditional insert

At 03:51 AM 9/8/2011, Merlin Moncure wrote:
> > Don't you have to block SELECTs so that the SELECTs get serialized?
> > Otherwise concurrent SELECTs can occur at the same time, find no existing
> > rows, then "all" the inserts proceed and you get errors (or dupes).
> >
> > That's how Postgresql still works right? I haven't really been keeping up.
>
>yeah -- but you only need to block selects if you are selecting in the
>inserting transaction (this is not a full upsert). if both writers
>are doing:
>begin;
>lock table foo exclusive;
>insert into foo select ... where ...;
>commit;
>
>is good enough. btw even if you are doing upsert pattern
>(lock...select for update...insert/update), you'd be fine with
>straight exclusive locks because the 'for update' lock takes a higher
>lock that is blocked by exclusive. A basic rule of thumb is to try
>and not fully block readers unless absolutely necessary...basically
>maintenance operations.

Oh wait, now I think I get it. "lock table foo exclusive" will block
the inserts too, so I wouldn't get dupe errors even if other
transactions "blindly" insert dupes at the same time. The other
transactions might get the dupe errors, but mine won't as long as it
selects first and only inserts if there are no rows at that point.

Is that correct?

Link.