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(at)debian(dot)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(at)debian(dot)org |
| http://people.debian.org/~pik/ |

paul cannon <pik(at)debian(dot)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?
>
>
>