Thread: 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@serensoft.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@serensoft.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@serensoft.com http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us!