From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | will trillich <will(at)serensoft(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: RULE vs TRIGGER |
Date: | 2001-07-30 17:05:39 |
Message-ID: | Pine.BSF.4.21.0107300956200.95589-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-07-30 18:12:12 | Re: Re: "Oracle's ROWNUM" |
Previous Message | Stephan Szabo | 2001-07-30 16:51:46 | Re: referential integrity violation |