Thread: [BUGS] bug in RULE insert

<br /><br />Strange the rule works for an insertion<br /><br /><br />example:<br /><br /><p>CREATE TABLE public.test<br
/>(<br/>id bigserial NOT NULL,<br />name text<br />);<p><br />ALTER TABLE public.test<br />ADD CONSTRAINT
test_constraint_pkeyPRIMARY KEY(id);<p><br />CREATE TABLE public.v_test<br />(<br />id bigserial NOT NULL,<br />v_id
bigint,<br/>v_name text<br />);<p><br />ALTER TABLE public.v_test<br />ADD CONSTRAINT v_test_constraint_pkey PRIMARY
KEY(id);<p><br/>CREATE OR REPLACE RULE insert AS<br />ON INSERT TO test DO INSERT INTO v_test (v_id, v_name)<br
/>VALUES(new.id, new.name);<p><br /><br /><br />then execute<br /><br /><p>insert into test(name)<br />values <br
/>('1'),<br/>('2'),<br />('3')<br /><br />values in test.id <> v_test.v_id<p>Tested on postgresql 9.5/9.6 install
fromPPA  <span data-mce-style="font-family: monospace; font-size: 10.764px; background-color: #f7f7f7;"
style="font-family:monospace; font-size: 10.764px; background-color:
#f7f7f7;">http://apt.postgresql.org/pub/repos/apt</span><br/>OS ubuntu-sever 14.04/16.04<p>Bbest regards<br />Alexander
Pokolenko.<br/> 

Александр <alexander_8901@mail.ru> writes:
> Strange the rule works for an insertion

This is expected behavior, because the rule works like a macro, and
you have a volatile argument (that is, the nextval() call for the
serial column's default) being passed to it and thereby being executed
twice.  IOW, what you wrote is equivalent to

insert into test(id, name)
values 
(nextval('test_id_seq'), '1'),
(nextval('test_id_seq'), '2'),
(nextval('test_id_seq'), '3');

and that executes, then the rule causes this to also be executed:

insert into v_test (v_id, v_name)
values 
(nextval('test_id_seq'), '1'),
(nextval('test_id_seq'), '2'),
(nextval('test_id_seq'), '3');

What you seem to want would be much more reliably done with a trigger.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs