CODEWITHSUNDEEP
sqlpostgresql
alexander_8901
alexander_8901

Reputation: 41

Strange the rule works for an insertion on PostgreSql 9.5/9.6

example:

CREATE TABLE public.test
(
id bigserial NOT NULL,
name text
);


ALTER TABLE public.test
ADD CONSTRAINT test_constraint_pkey PRIMARY KEY(id);


CREATE TABLE public.v_test
(
id bigserial NOT NULL,
v_id bigint,
v_name text
);


ALTER TABLE public.v_test
ADD CONSTRAINT v_test_constraint_pkey PRIMARY KEY(id);


CREATE OR REPLACE RULE insert AS
ON INSERT TO test DO INSERT INTO v_test (v_id, v_name)
VALUES (new.id, new.name);

then execute

insert into test(name)
values 
('1'),
('2'),
('3')

values in test.id <> v_test.v_id

Tested on postgresql 9.5/9.6 install from PPA http://apt.postgresql.org/pub/repos/apt

OS ubuntu-sever 14.04/16.04

Upvotes: 1

Views: 51

Answers (2)

alexander_8901
alexander_8901

Reputation: 41

Actually not the case. That's what said in the Postgresql support

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.

Upvotes: 1

JosMac
JosMac

Reputation: 2312

Yes, I see your point - I tested it and I always get into v_test.v_id next ID value from sequence. If you try to insert only one new record into test table it is more visible. Unfortunately rule cannot reference old.id value.

Upvotes: 0

Related Questions