Re: RULE vs TRIGGER

On Mon, 30 Jul 2001, will trillich wrote:

> i have a solution using RULES and PLGPSQL functions (instead of
> triggers) for insert-unless-found, using perl lingo:
>
>     # perlish pseudoCode
>     unless (select(tbl.fld == val)) { insert tbl.fld = val };
>
> i'd love to hear the skinny on why the following is a bad idea,
> which i presume it is because 1) it works and 2) i understand
> it:

ISTM, in general, the above construct is not safe for general use. Say
you have two transactions:

Transaction 1 start
Transaction 2 start
Transaction 1 selects on tbl, gets no rows
Transaction 2 selects on tbl, gets no rows
Transaction 1 inserts
Transaction 2 inserts
Transaction 1 commits
Transaction 2 commits

Both transactions would do an insert (not seeing the other) and you'd
have two lookup values for the same val.  I think you'd need an explicit
lock on tbl to make it safe.