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

Поиск
Список
Период
Сортировка
От quassnoi@gmail.com
Тема BUG #14089: ON CONFLICT allows function variables in index expressions
Дата
Msg-id 20160415213614.22913.56001@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #14089: ON CONFLICT allows function variables in index expressions  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: BUG #14089: ON CONFLICT allows function variables in index expressions  (Peter Geoghegan <pg@heroku.com>)
Список 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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14087: btree_gin index doesn't work on INT with POSITIVE constraint
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #14089: ON CONFLICT allows function variables in index expressions