Re: RULE vs TRIGGER

From: will trillich <will(at)serensoft(dot)com>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: RULE vs TRIGGER
Date: 2001-07-31 17:24:25
Message-ID: 3B66E9C8.D6A89C96@serensoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)serensoft(dot)com
http://www.dontUthink.com/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joel Burton 2001-07-31 17:33:15 Re: ODBC read/write permission in MS Access
Previous Message Fran Fabrizio 2001-07-31 17:13:00 Re: looking for a secure