BUG #14089: ON CONFLICT allows function variables in index expressions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14089
Logged by:          Alex Bolenok
Email address:      quassnoi@gmail.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.