From: paul cannon <pik@debian.org>
To: pgsql-sql@postgresql.org
Subject: rule causes nextval() to be invoked twice
Date: Tue, 22 Jul 2003 19:47:00 -0600
Message-ID: <20030723014700.GE24912@fslc.usu.edu> (raw)
'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/ |
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pik@debian.org
Subject: Re: rule causes nextval() to be invoked twice
In-Reply-To: <20030723014700.GE24912@fslc.usu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox