Re: conditional insert

At 03:51 AM 9/8/2011, Merlin Moncure wrote:
>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.

Yeah it works if all the inserters do the lock table (or select for 
update), and provides better performance.

But if you're paranoid and lazy - a full lock will ensure that your 
code won't get dupe errors even if someone else's code or manual 
control doesn't do the lock table (they might get the dupe errors[1], 
but that's their problem ;) ). So your code can safely assume that 
any DB errors that occur are those that deserve a full rollback of 
everything (which is what Postgresql "likes" by default). This means 
fewer scenarios to handle so you don't need to write as much code, 
nor document and support as much code ;).

Link.

[1] I'm assuming a unique constraint is present- the locking is to 
simplify things.