RULE vs TRIGGER

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:

-- mostly static lookup table:
create TABLE lookup(
id serial,
val varchar(50),
primary key( id )
);

-- dynamic data (lots of traffic here):
create TABLE _real_data (
-- ...
lookup integer
references lookup(id),
-- ...
);

-- a view to tie them together:
create VIEW see_data as
select
-- _real_data.* ...
l.val as lookup, -- display text, not id
-- ...
from
_real_data r,
lookup l
where
r.lookup = l.id;

-- here's the workhorse:
create FUNCTION get_lookup(varchar) returns integer as '
declare
t alias for $1;
i integer;
begin
-- maybe it exists already:
select into i
id
from lookup
where val = t;
-- if not, create it:
if not found then
insert into lookup (val) values (t);
i := currval(''lookup_id_seq'');
end if;
-- return its id:
return i;
end;' language 'plpgsql'; --'

-- and here's the capstone:
create RULE new_data as
on insert to see_data
do instead [
insert into _real_data (
-- ...
lookup,
-- ...
) values (
-- ...
get_lookup( NEW.lookup ), -- normalize text as ID# instead
-- ...
)
];

something tells me that calling a pl/pgsql function in the
middle of an INSERT -- a function that might do a SELECT and an
INSERT of its own -- might somehow be A Bad Thing, because it
works like a charm.

bad dog?

--
I'd concentrate on "living in the now" because it is fun
and on building a better world because it is possible.
- Tod Steward

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

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.

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/

On Tue, 31 Jul 2001, will trillich wrote:

> Stephan Szabo wrote:
> >
> > 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 meant using a lock table statement explicitly at the
beginning of the trigger (LOCK TABLE tbl; -- possibly
would have to be through execute, I'm not sure) which
I would presume would mean that the "second" would
have to wait at that point until the first transaction
finished completely. Of course this cuts down your
concurrency as only one transaction calling this would
be able to run and the rest would have to wait.

On Tue, Jul 31, 2001 at 11:05:37AM -0700, Stephan Szabo wrote:
> I meant using a lock table statement explicitly at the
> beginning of the trigger (LOCK TABLE tbl; -- possibly
> would have to be through execute, I'm not sure) which
> I would presume would mean that the "second" would
> have to wait at that point until the first transaction
> finished completely. Of course this cuts down your
> concurrency as only one transaction calling this would
> be able to run and the rest would have to wait.

hmm. so, how about

create rule
add_new_item as
on insert to fake_view
do instead (
begin work;
lock table _real_data;
insert into _real_data ( ... ) values ( ... );
commit work;
);

would something like this be legal...? (i.e. what's the syntax
necessary to make it happen behind-the-scenes?)

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!