[SQL] rule causes nextval() to be invoked twice

[paul cannon]

'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 [EMAIL PROTECTED] |
| http://people.debian.org/~pik/ |

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] rule causes nextval() to be invoked twice

[paul cannon]

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 [EMAIL PROTECTED] |
| http://people.debian.org/~pik/ |

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])