Hi,
Hugo Mercier (in Cc) reported me an error in a query, failing since pg10.
Simple test case to reproduce:
CREATE TABLE public.test (id integer);
WITH test AS (select 42) INSERT INTO public.test SELECT * FROM test;
which will fail with "relation "test" cannot be the target of a
modifying statement".
IIUC, that's an oversight in 18ce3a4ab22, where setTargetTable()
doesn't exclude qualified relation when searching for special
relation.
PFA a simple patch to fix this issue, with updated regression test.
Regards.
diff --git a/src/backend/parser/parse_clause.c
b/src/backend/parser/parse_clause.c
index 9ff80b8b40..48a065e41c 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -181,11 +181,17 @@ int
setTargetTable(ParseState *pstate, RangeVar *relation,
bool inh, bool alsoSource, AclMode requiredPerms)
{
- RangeTblEntry *rte;
+ RangeTblEntry *rte = NULL;
int rtindex;
+ /*
+ * if it is an unqualified name, it might be a CTE or tuplestore
+ * reference
+ */
+ if (!relation->schemaname)
+ rte = getRTEForSpecialRelationTypes(pstate, relation);
+
/* So far special relations are immutable; so they cannot be targets. */
- rte = getRTEForSpecialRelationTypes(pstate, relation);
if (rte != NULL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
diff --git a/src/test/regress/expected/with.out
b/src/test/regress/expected/with.out
index c32a490580..53ea9991b2 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2275,3 +2275,7 @@ with ordinality as (select 1 as x) select * from
ordinality;
-- check sane response to attempt to modify CTE relation
WITH d AS (SELECT 42) INSERT INTO d VALUES (1);
ERROR: relation "d" cannot be the target of a modifying statement
+-- check qualified relation name doesn't conflict with CTE name
+CREATE TABLE public.self (id integer);
+WITH self AS (SELECT 42) INSERT INTO public.self SELECT * from self;
+DROP TABLE public.self;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 8ae5184d0f..17f32c3c87 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1031,3 +1031,8 @@ with ordinality as (select 1 as x) select * from
ordinality;
-- check sane response to attempt to modify CTE relation
WITH d AS (SELECT 42) INSERT INTO d VALUES (1);
+
+-- check qualified relation name doesn't conflict with CTE name
+CREATE TABLE public.self (id integer);
+WITH self AS (SELECT 42) INSERT INTO public.self SELECT * from self;
+DROP TABLE public.self;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
