From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | lizardus(at)ya(dot)ru |
Subject: | BUG #15855: Using 'nextval' inside INSERT RULE in case of bulk data insertion. |
Date: | 2019-06-17 08:04:06 |
Message-ID: | 15855-52f9fbeb62a30b76@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15855
Logged by: Ivan
Email address: lizardus(at)ya(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Vianello, Daniel A | 2019-06-17 14:47:49 | RE: Not able to insert data into table |
Previous Message | shruti.gupta | 2019-06-17 05:50:57 | Not able to insert data into table |