Обсуждение: BUG #15855: Using 'nextval' inside INSERT RULE in case of bulk data insertion.
BUG #15855: Using 'nextval' inside INSERT RULE in case of bulk data insertion.
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15855 Logged by: Ivan Email address: lizardus@ya.ru PostgreSQL version: 10.8 Operating system: not matter Description: I use two SQL commands in RULE INSTEAD INSERT. I expect that in case of bulk data insertion they will be sequentially executed for each record. I expect that during a bulk insert, they will be sequentially executed for each record. However, firstly the first SQL-command is executed all records, then the second for all records. As a result, I get one value for all 'nextval' calls. Script for reproduce: ---------------------------------------- CREATE SEQUENCE s; CREATE TABLE t1 ( id integer default nextval('s') primary key, col1 integer ); create table t2 ( id integer references t1(id), col2 integer ); create view v1 as ( select * from t1 left join t2 using(id) ); CREATE OR REPLACE RULE "_INSERT" AS ON INSERT TO v1 DO INSTEAD ( INSERT INTO t1 VALUES (nextval('s'::regclass),NEW.col1); INSERT INTO t2 VALUES (currval('s'::regclass),NEW.col2); ); insert into v1 (col1,col2) select a, b from (values(10,11),(12,13),(14,15),(16,17),(18,19),(20,21)) t(a,b); select * from v1 order by id; ------------------------------------------- RESULT: 1;10;NULL 2;12;NULL 3;14;NULL 4;16;NULL 5;18;NULL 6;20;11 6;20;21 6;20;13 6;20;15 6;20;17 6;20;19 EXPECTED RESULT: 1;10;11 2;12;13 3;14;15 4;16;17 5;18;19 6;20;21
Re: BUG #15855: Using 'nextval' inside INSERT RULE in case of bulk data insertion.
От
Andrew Gierth
Дата:
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes: PG> I use two SQL commands in RULE INSTEAD INSERT. I expect that in PG> case of bulk data insertion they will be sequentially executed for PG> each record. I expect that during a bulk insert, they will be PG> sequentially executed for each record. However, firstly the first PG> SQL-command is executed all records, then the second for all PG> records. As a result, I get one value for all 'nextval' calls. This isn't a bug, it's just how rules work. Use triggers instead. https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_rules -- Andrew (irc:RhodiumToad)