From 617db3a2d8ad6abc0d7be623f43edc943a6640a8 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Mon, 28 Sep 2015 19:12:48 +0200 Subject: [PATCH] Fix ON CONFLICT DO UPDATE for tables with oids. When taking the UPDATE path in an INSERT .. ON CONFLICT .. UPDATE tables with oids were not supported. The tuple generated by the update target list was projected without space for an oid - a simple oversight. Reported-By: Peter Geoghegan Author: Andres Freund Backpatch: 9.5, where ON CONFLICT was introduced --- src/backend/executor/nodeModifyTable.c | 2 +- src/test/regress/expected/insert_conflict.out | 59 +++++++++++++++++++ src/test/regress/sql/insert_conflict.sql | 22 +++++++ 3 files changed, 82 insertions(+), 1 deletion(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 1ef76d0928..dabaea9910 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -1678,7 +1678,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* create target slot for UPDATE SET projection */ tupDesc = ExecTypeFromTL((List *) node->onConflictSet, - false); + resultRelInfo->ri_RelationDesc->rd_rel->relhasoids); mtstate->mt_conflproj = ExecInitExtraTupleSlot(mtstate->ps.state); ExecSetSlotDescriptor(mtstate->mt_conflproj, tupDesc); diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 09b67db43c..1399f3c796 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -507,3 +507,62 @@ insert into excluded values(1, '2') on conflict (key) do update set data = 3 RET -- clean up drop table excluded; +-- Check tables w/o oids are handled correctly +create table testoids(key int primary key, data text) without oids; +-- first without oids +insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 1 | 1 +(1 row) + +insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 1 | 2 +(1 row) + +-- add oids +alter table testoids set with oids; +-- update existing row, that didn't have an oid +insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 1 | 3 +(1 row) + +-- insert a new row +insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 2 | 1 +(1 row) + +-- and update it +insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 2 | 2 +(1 row) + +-- remove oids again, test +alter table testoids set without oids; +insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 1 | 4 +(1 row) + +insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 3 | 1 +(1 row) + +insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 3 | 2 +(1 row) + +DROP TABLE testoids; diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index e981e67fd2..efa902ec12 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -295,3 +295,25 @@ insert into excluded values(1, '2') on conflict (key) do update set data = 3 RET -- clean up drop table excluded; + + +-- Check tables w/o oids are handled correctly +create table testoids(key int primary key, data text) without oids; +-- first without oids +insert into testoids values(1, '1') on conflict (key) do update set data = excluded.data RETURNING *; +insert into testoids values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; +-- add oids +alter table testoids set with oids; +-- update existing row, that didn't have an oid +insert into testoids values(1, '3') on conflict (key) do update set data = excluded.data RETURNING *; +-- insert a new row +insert into testoids values(2, '1') on conflict (key) do update set data = excluded.data RETURNING *; +-- and update it +insert into testoids values(2, '2') on conflict (key) do update set data = excluded.data RETURNING *; +-- remove oids again, test +alter table testoids set without oids; +insert into testoids values(1, '4') on conflict (key) do update set data = excluded.data RETURNING *; +insert into testoids values(3, '1') on conflict (key) do update set data = excluded.data RETURNING *; +insert into testoids values(3, '2') on conflict (key) do update set data = excluded.data RETURNING *; + +DROP TABLE testoids; -- 2.40.0