From: | quassnoi(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14089: ON CONFLICT allows function variables in index expressions |
Date: | 2016-04-15 21:36:14 |
Message-ID: | 20160415213614.22913.56001@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14089
Logged by: Alex Bolenok
Email address: quassnoi(at)gmail(dot)com
PostgreSQL version: 9.5.2
Operating system: CentOS 6
Description:
test=# CREATE TABLE test (id BIGSERIAL NOT NULL PRIMARY KEY, value INT);
CREATE TABLE
test=# CREATE UNIQUE INDEX ix_test ON test (value, (1));
CREATE INDEX
test=# INSERT INTO test (value) VALUES (1);
INSERT 0 1
test=# INSERT INTO test (value) VALUES (1);
ERROR: duplicate key value violates unique constraint "ix_test"
ПОДРОБНОСТИ: Key (value, (1))=(1, 1) already exists.
test=# INSERT INTO test (value) VALUES (1) ON CONFLICT (value, (1)) DO
NOTHING;
INSERT 0 0
test=# INSERT INTO test (value) VALUES (1) ON CONFLICT (value, (2)) DO
NOTHING;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT
specification
test=# INSERT INTO test (value) VALUES (1) ON CONFLICT (value, (id)) DO
NOTHING;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT
specification
test=# INSERT INTO test (value) VALUES (1) ON CONFLICT (value,
(no_such_column)) DO NOTHING;
ERROR: column "no_such_column" does not exist
СТРОКА 1: ...INTO test (value) VALUES (1) ON CONFLICT (value,
(no_such_co...
test=# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON CONFLICT
(value, (n)) DO NOTHING;
ERROR: column "n" does not exist
СТРОКА 1: ...CT * FROM (VALUES (1)) q (n) ON CONFLICT (value, (n)) DO
NOT...
ПОДСКАЗКА: There is a column named "n" in table "*SELECT*", but it cannot
be referenced from this part of the query.
test=# DROP FUNCTION IF EXISTS fn_test(INT); CREATE FUNCTION fn_test(n INT)
RETURNS VOID AS $$ INSERT INTO test (value) VALUES (1) ON CONFLICT (value,
(n)) DO NOTHING; $$ LANGUAGE 'sql';
DROP FUNCTION
CREATE FUNCTION
test=# SELECT * FROM fn_test(1);
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT
specification
КОНТЕКСТ: SQL function "fn_test" during startup
I expected CREATE FUNCTION to fail with the same messages as the INSERT
query before it, because it makes no sense to reference function variables
in index inference.
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-04-15 22:04:17 | Re: BUG #14089: ON CONFLICT allows function variables in index expressions |
Previous Message | Tom Lane | 2016-04-15 21:09:16 | Re: BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint |