Thread: rule causes nextval() to be invoked twice
'Sup list- I'm having trouble understanding the behavior of rules with regards to default values. Here's my situation: I have a table with a column referencing another. When inserts are made to the second, I would like a certain corresponding insert made to the first. Here's the simplest case I can think of: -- Begin demo SQL CREATE TABLE main (id SERIAL PRIMARY KEY,contents VARCHAR); CREATE TABLE othertable (main_id INTEGER REFERENCES main ); CREATE RULE main_insert AS ON INSERT TO main DO INSERT INTO othertable VALUES (new.id); INSERT INTO main(contents) VALUES ('Fails here'); -- End demo SQL The last INSERT fails with: "$1 referential integrity violation - key referenced from othertable not found in main" If I remove the REFERENCES constraint, then I can see why. The insert made into main behaves as expected; it gets nextval('main_id_seq'), which comes out to 1. However, the main_insert rule gets _another_ nextval('main_id_seq'), and the value 2 is inserted into othertable. "select nextval('main_id_seq')" afterwards confirms that the sequence was incremented twice by the INSERT. Is PostgreSQL supposed to be behaving that way? If so, what is the reasoning behind it? Is there any way I can get around that and still use a SERIAL for my primary key? Until then, I'll have to make a function to do nextval('main_id_seq') with every insert, and have the primary key be INTEGER. Thanks- -- .------------------------------------------------------------. | paul cannon pik@debian.org | | http://people.debian.org/~pik/ |
On Tue, Jul 22, 2003 at 07:47:00PM -0600, paul cannon wrote: > Until then, I'll have to make a function to do nextval('main_id_seq') > with every insert, and have the primary key be INTEGER. Nevermind- that doesn't work either! Here's the new sample code: -- Begin demo SQL CREATE SEQUENCE main_id_seq; CREATE TABLE main ( id INTEGER PRIMARY KEY, contents VARCHAR ); CREATE TABLE othertable ( main_id INTEGER REFERENCES main(id) ); CREATE RULE main_insert AS ON INSERT TO main DO INSERT INTO othertable VALUES (new.id); INSERT INTO main(id, contents) VALUES (nextval('main_id_seq'), 'Fails here'); -- End demo SQL The same thing happens. The rule tries to put 2 into othertable. Surely this is a bug? -- .------------------------------------------------------------. | paul cannon pik@debian.org | | http://people.debian.org/~pik/ |
paul cannon <pik@debian.org> writes: > I'm having trouble understanding the behavior of rules with regards to > default values. > ... > If I remove the REFERENCES constraint, then I can see why. The insert > made into main behaves as expected; it gets nextval('main_id_seq'), > which comes out to 1. However, the main_insert rule gets _another_ > nextval('main_id_seq'), and the value 2 is inserted into othertable. Yeah. Rules are macros, and as such have the usual issues about multiple evaluations of arguments. Arguments with side effects are bad news. It looks to me like what you are trying to do is reflect a copy of an inserted row into a log table. You'd be better advised to do this with a trigger ... probably an AFTER trigger, so that you know exactly what got inserted. (BEFORE triggers have to consider the possibility that they're not the last BEFORE trigger.) regards, tom lane
> Nevermind- that doesn't work either! Here's the new sample code: Rules are triggered before the event. You must do it in AFTER trigger. regards, bhuvaneswaran
I think, your example would work if you replaced the new.id in the rule with curval ('main_id_seq'); ... but see Tom's earlier reply - this is still not a very good thing to do... For example, it won't work if you try to insert into main anything with explicitly specified id (not generated by the sequence), or if you insert multiple rows with the single statement (like insert... select), or if you do COPY (besides the fact that it doesn't touch sequence, it also doesn't invoke rules at all). To do what you are trying to do, an after trigger seems to be the only thing that will work completely. Dima paul cannon wrote: >On Tue, Jul 22, 2003 at 07:47:00PM -0600, paul cannon wrote: > > >>Until then, I'll have to make a function to do nextval('main_id_seq') >>with every insert, and have the primary key be INTEGER. >> >> > >Nevermind- that doesn't work either! Here's the new sample code: > >-- Begin demo SQL > >CREATE SEQUENCE main_id_seq; >CREATE TABLE main ( > id INTEGER PRIMARY KEY, > contents VARCHAR >); > >CREATE TABLE othertable ( > main_id INTEGER REFERENCES main(id) >); > >CREATE RULE main_insert AS > ON INSERT TO main DO > INSERT INTO othertable VALUES (new.id); > >INSERT INTO main(id, contents) VALUES (nextval('main_id_seq'), 'Fails here'); > >-- End demo SQL > >The same thing happens. The rule tries to put 2 into othertable. Surely >this is a bug? > > >