Reliability of CURRVAL in a RULE
Is the use of CURRVAL in this example reliable in heavy use?
CREATE TABLE users (
id SERIAL NOT NULL,
email VARCHAR(24) DEFAULT NULL,
PRIMARY KEY (id)
);
CREATE TABLE users_with_email (
id INTEGER NOT NULL
);
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (CURRVAL('users_id_seq'));
I tried...
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (NEW.id);
which was incrementing the sequence twice. Should I be using a trigger
instead? This rule seems quite simple and easy enough... if reliable. -
Nick
Nick wrote:
Is the use of CURRVAL in this example reliable in heavy use?
Nick - the hackers list is for people interested in working on the
code-base of PostgreSQL itself. This would have been better on the
general or sql lists.
CREATE RULE add_email AS ON INSERT TO users WHERE (NEW.email IS NULL)
DO INSERT INTO users_with_email (id) VALUES (CURRVAL('users_id_seq'));
Short answer no. Rules are like macros and you can end up with
unexpected multiple evaluations and strange order of execution. See the
mailing list archives for details and try inserting multiple users in
one go to see an example of a problem.
--
Richard Huxton
Archonet Ltd