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:

> 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:
>

​[snip]

> ​
>
> 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...
>
> ​[snip]
​

> 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.
>

I'm not sure I can explain this adequately but regardless this doesn't seem
like it qualifies as a bug.

As far as the INSERT inside the function is concerned you've provided a
constant integer in place of the "n". Since a constant integer is not a
column name any error referencing column name - which the referenced one
does - would be incorrect.

The INSERT doesn't know where the constant integer came from and the
function interpreter doesn't understand "index inference" - it just
replaces non-string-embedded instances of a variable name with the
corresponding value.

There is a degree of separation of concerns here this, while somewhat
problematic when it comes to accurately describing exact causes of errors,
is desirable from an overall architecture and execution standpoint. Given
that both scenarios are, correctly, errors, the difference in messages are
because of the execution environment and thus have every right to be
different.

David J.