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

On Fri, Apr 15, 2016 at 2:36 PM, <quassnoi(at)gmail(dot)com> wrote:
> 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.

In the second example, the argument to the function is not represented
as a Var from an RTE during parse analysis. It is not a column in any
table, or even something that's column-like.

I don't think this is a bug. I guess it's a bit weird that the SQL
function argument is treated as something that inference considers as
a constant, but even if that is downright wrong it seems very unlikely
to cause any actual problem. This is a very contrived scenario. You
can make SQL function inlining do slightly odd things like this in
other places, too.

--
Peter Geoghegan