Thread: 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