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
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 |