Re: RULE vs TRIGGER

Stephan Szabo wrote:
>
> 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

aha. boom, integrity check failure. hmm.

> 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.

is that something that the trigger method manages to
circumvent somehow? (i presume 'explicit table lock'
is covered on a page of documentation i haven't run
across yet...)

i'm using 7.1, by the way.

--
mailto:will@serensoft.com
http://www.dontUthink.com/