pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18502: Upsert on view returns 42P10 error when condition is an expression
3+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2024-06-11 915de706d2 9256bf6eb3 5e8aa32a9c b188e1bf75 1d0399b540 096f2132c6 Fix infer_arbiter_indexes() to not assume resultRelation is 1.
* BUG #18502: Upsert on view returns 42P10 error when condition is an expression
@ 2024-06-11 09:01 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-06-11 09:01 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: michael.wanghai.a@outlook.com
The following bug has been logged on the website:
Bug reference: 18502
Logged by: Michael Wang
Email address: michael.wanghai.a@outlook.com
PostgreSQL version: 12.5
Operating system: Official docker Image run in MacOS 14.3
Description:
I have a table, index and view like following:
```
CREATE TABLE my_table
(
id uuid primary key,
data jsonb not null
);
CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
CREATE VIEW my_view AS SELECT * FROM my_table;
```
The upsert on view returns 42P10 error when I execute the following SQL
```
INSERT INTO my_view (id, data)
VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
'{
"key": "value"
}'::jsonb)
ON CONFLICT ((data ->> 'key'))
DO NOTHING;
```
I tested cast expression and fails as well
```
CREATE UNIQUE INDEX ON my_table ((id::text));
INSERT INTO my_view (id, data)
VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
'{
"key": "value"
}'::jsonb)
ON CONFLICT ((id::text))
DO NOTHING;
```
I also tested `DO UPDATE...`, the same error is returned.
I also tested with the latest official docker image witch is PG 16, same
error returns.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18502: Upsert on view returns 42P10 error when condition is an expression
@ 2024-06-11 20:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-06-11 20:54 UTC (permalink / raw)
To: michael.wanghai.a@outlook.com; +Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> I have a table, index and view like following:
> ```
> CREATE TABLE my_table
> (
> id uuid primary key,
> data jsonb not null
> );
> CREATE UNIQUE INDEX ON my_table ((data ->> 'key'));
> CREATE VIEW my_view AS SELECT * FROM my_table;
> ```
> The upsert on view returns 42P10 error when I execute the following SQL
> ```
> INSERT INTO my_view (id, data)
> VALUES ('990cc75c-2e60-4c0d-8bec-9ac976dc03bc'::uuid,
> '{
> "key": "value"
> }'::jsonb)
> ON CONFLICT ((data ->> 'key'))
> DO NOTHING;
> ```
For the archives' sake: the error being complained of is
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Thanks for the report. The fault lies with infer_arbiter_indexes(),
which assumes that it can match the output of
RelationGetIndexExpressions or RelationGetIndexPredicate
directly to the query without adjusting varnos. That works
most of the time, because the INSERT target typically has
varno 1, matching the way these trees are stored in the catalogs.
But not so much when we've flattened an updatable view;
so the match fails even when it should succeed.
The attached seems to be enough to fix it.
regards, tom lane
Attachments:
adjust-varnos-in-infer_arbiter_indexes.patch (text/x-diff, 6.1K) download
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index a51fc34e6e..775955363e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -695,6 +695,7 @@ infer_arbiter_indexes(PlannerInfo *root)
OnConflictExpr *onconflict = root->parse->onConflict;
/* Iteration state */
+ Index varno;
RangeTblEntry *rte;
Relation relation;
Oid indexOidFromConstraint = InvalidOid;
@@ -723,7 +724,8 @@ infer_arbiter_indexes(PlannerInfo *root)
* the rewriter or when expand_inherited_rtentry() added it to the query's
* rangetable.
*/
- rte = rt_fetch(root->parse->resultRelation, root->parse->rtable);
+ varno = root->parse->resultRelation;
+ rte = rt_fetch(varno, root->parse->rtable);
relation = table_open(rte->relid, NoLock);
@@ -857,6 +859,9 @@ infer_arbiter_indexes(PlannerInfo *root)
/* Expression attributes (if any) must match */
idxExprs = RelationGetIndexExpressions(idxRel);
+ if (idxExprs && varno != 1)
+ ChangeVarNodes((Node *) idxExprs, 1, varno, 0);
+
foreach(el, onconflict->arbiterElems)
{
InferenceElem *elem = (InferenceElem *) lfirst(el);
@@ -908,6 +913,8 @@ infer_arbiter_indexes(PlannerInfo *root)
* CONFLICT's WHERE clause.
*/
predExprs = RelationGetIndexPredicate(idxRel);
+ if (predExprs && varno != 1)
+ ChangeVarNodes((Node *) predExprs, 1, varno, 0);
if (!predicate_implied_by(predExprs, (List *) onconflict->arbiterWhere, false))
goto next;
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 701217ddbc..5cb9cde030 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -2,6 +2,8 @@
-- insert...on conflict do unique index inference
--
create table insertconflicttest(key int4, fruit text);
+-- These things should work through a view, as well
+create view insertconflictview as select * from insertconflicttest;
--
-- Test unique index inference with operator class specifications and
-- named collations
@@ -43,6 +45,15 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
-> Result
(4 rows)
+explain (costs off) insert into insertconflictview values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing;
+ QUERY PLAN
+-------------------------------------------------
+ Insert on insertconflicttest
+ Conflict Resolution: NOTHING
+ Conflict Arbiter Indexes: both_index_expr_key
+ -> Result
+(4 rows)
+
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit
where exists (select 1 from insertconflicttest ii where ii.key = excluded.key);
QUERY PLAN
@@ -380,6 +391,7 @@ create unique index partial_key_index on insertconflicttest(key) where fruit lik
-- Succeeds
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit;
insert into insertconflicttest as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing;
+insert into insertconflictview as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing;
-- fails
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
@@ -445,6 +457,7 @@ explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') o
drop index plain;
-- Cleanup
+drop view insertconflictview;
drop table insertconflicttest;
--
-- Verify that EXCLUDED does not allow system column references. These
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 653f9baf98..549c46452e 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -3,6 +3,9 @@
--
create table insertconflicttest(key int4, fruit text);
+-- These things should work through a view, as well
+create view insertconflictview as select * from insertconflicttest;
+
--
-- Test unique index inference with operator class specifications and
-- named collations
@@ -20,6 +23,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing;
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing;
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing;
+explain (costs off) insert into insertconflictview values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing;
explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit
where exists (select 1 from insertconflicttest ii where ii.key = excluded.key);
-- Neither collation nor operator class specifications are required --
@@ -218,6 +222,7 @@ create unique index partial_key_index on insertconflicttest(key) where fruit lik
-- Succeeds
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit;
insert into insertconflicttest as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing;
+insert into insertconflictview as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing;
-- fails
insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit;
@@ -250,6 +255,7 @@ explain (costs off) insert into insertconflicttest as i values (23, 'Avocado') o
drop index plain;
-- Cleanup
+drop view insertconflictview;
drop table insertconflicttest;
=
* Re: BUG #18502: Upsert on view returns 42P10 error when condition is an expression
@ 2024-06-12 02:18 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2024-06-12 02:18 UTC (permalink / raw)
To: 王 海 <michael.wanghai.a@outlook.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tue, Jun 11, 2024 at 7:11 PM 王 海 <michael.wanghai.a@outlook.com> wrote:
> Thanks for the analysis. May I ask how long will it cost normally for such
> bug fix being released since I'm new to the community? And will it be back
> port to PG 12?
>
>
August according to the published roadmap:
https://www.postgresql.org/developer/roadmap/
And yes this one was back-patched to all supported versions which includes
v12 - though its time is up this year once v17 is released.
https://www.postgresql.org/support/versioning/
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-06-12 02:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-11 09:01 BUG #18502: Upsert on view returns 42P10 error when condition is an expression PG Bug reporting form <noreply@postgresql.org>
2024-06-11 20:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-06-12 02:18 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox