Problem with a sequence being acted on by an on insert rule.
От | Mark Le Huray |
---|---|
Тема | Problem with a sequence being acted on by an on insert rule. |
Дата | |
Msg-id | 1036514313.5617.35.camel@markspc обсуждение исходный текст |
Ответы |
Re: Problem with a sequence being acted on by an on insert
Re: Problem with a sequence being acted on by an on insert rule. |
Список | pgsql-bugs |
Apologies if this bug has already been reported and I am also reasonably new to postgresql so I might be doing something stupid :-) Anyway to replicate the problem: Initial tables: - create sequence autonumber increment 1 minvalue 0 start 0; - create table testtable1 ( pk int primary key ); - create table testtable2 ( fk int primary key references testtable1(pk)); - create rule updatetesttable2 as on insert to testtable1 do (insert into testable2 (fk) values( new.pk )); Testing: - insert into testtable1 values ('1'); - select * from testtable2; <produces> fk ---- 1 (1 row) The Problem: - insert into testtable1 values ( (select nextval('autonumber'))); <produces> ERROR: <unnamed> referential integrity violation - key referenced from testtable2 not found in testtable1 This confused me for a while until I did some testing and removed the foreign key from testtable2 i.e. - drop table testtable2; - drop rule updatetesttable2; - create table testtable2 (fk int); - create rule updatetesttable2 as on insert to testtable1 do (insert into testable2 (fk) values( new.pk )); Now we have: - insert into testtable1 values ( ( select nextval('autonumber'))); - select * from testtable1; pk ----- 1 155 (2 rows) - select * from testtable2; fk ----- 156 (1 row) So in other words the rule action new.pk actually pulled the next number from the sequence autonumber thus failing the refential integrity checks above. There is no rush to fix this as I have modified the rule as follows which works but still wastes a sequence number: - create rule updatetesttable2 as on insert to testtable1 do (insert into testable2 (fk) values( new.pk - 1 )); Version information as follows: - Linux distro - Debian stable - Kernel version 2.4.19 - select version(); version --------------------------------------------------------------- PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) Thanks Mark
В списке pgsql-bugs по дате отправления: