rule causes nextval() to be invoked twice - Mailing list pgsql-sql

'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/ |