Interaction between ON CONFLICT index inference and prepared statement generic plans

From: "Bojan Petrovic" <bojan_petrovic(at)fastmail(dot)fm>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Interaction between ON CONFLICT index inference and prepared statement generic plans
Date: 2020-07-13 09:21:08
Message-ID: 37af9940-22ec-49dc-8194-72f65bc80354@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I noticed a behavior that was surprising to me,
so I wanted to know if such behavior is expected.

I created a partial unique index on table "t",
and a prepared statement in which a subexpression
of the index's WHERE clause figures as a parameter.

# CREATE TABLE t(i int);
# CREATE UNIQUE INDEX ON t(i) WHERE i >= 2;
# PREPARE u(int) AS
INSERT INTO t VALUES(3)
ON CONFLICT (i) WHERE i >= $1
DO NOTHING;

After performing "EXPLAIN EXECUTE u(2)" 5 times
and getting a valid query plan, the 6th execution
of the same statement results in:

# EXPLAIN EXECUTE u(2);
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

It seems that the attempt to create a generic
execution plan fails because inference is
impossible without a concrete value.

The documentation for "PREPARE" talks about prepared
statements being "semantically *almost* equivalent",
so I guess some change in statement meaning is
allowed (although in this case I'd say that the
statement loses meaning after 5th execution, because
it becomes invalid).

Regards,
Bojan

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2020-07-13 09:51:19 Re: Doubt in mvcc
Previous Message Rama Krishnan 2020-07-13 08:52:03 Re: Doubt in mvcc