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);

After doing

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

the values ​​in test.id do not agree with v_test.v_id

postgresql 9.5 / 9.6 I put from PPA

OS ubuntu-sever 14.04 / 16.04

  • one
    Perhaps v_test.id needs to be done bigint, and not bigserial? - ilyaplot
  • You did not understand, the rule writes the field test.id to v_test.v_id. and writes wrong - alexander_8901
  • I just understood correctly. Why use serial if you still write a certain bigint? - ilyaplot

1 answer 1

Values ​​do not converge in a very interesting way, namely the following nextval is used. If the insert id is set explicitly, the rule copies the data normally. Apparently, this is the problem, the second insert receives the default value of the column again.

It seems that the rule system works initially .

Experimenting

 create function testnotice() returns bigint as $$ begin raise notice 'executed'; return 1; end $$ language plpgsql; alter table test add column tescolumn bigint default testnotice(); alter table v_test add column tescolumn bigint; CREATE OR REPLACE RULE insert AS ON INSERT TO test DO INSERT INTO v_test (v_id, v_name, tescolumn) VALUES (new.id, new.name, new.tescolumn);

try to write something down:

 melkij=# insert into test(name) values ('6'); NOTICE: executed NOTICE: executed INSERT 0 1

So it is, the function for the default field is called twice. serial / bigserial, which are actually macros for default nextval(sequencename) , are therefore also called twice.

As new.* not the values ​​from the result of some query that are substituted, but the declaration of the columns directly: the values ​​specified explicitly in the insert or default query.

Use the after insert trigger instead. It will have the expected behavior and in NEW it will provide the values ​​of the already written string.

  • Or maybe there is an opportunity to work with RULE without calling nextval again? - alexander_8901
  • I do not know this. - Shallow