BUG #14089: ON CONFLICT allows function variables in index expressions

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.

Responses

Browse pgsql-bugs by date

  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