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

The function should not even compile, as the INSERT query before it does
not:

test=# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON CONFLICT
(value, (n)) DO NOTHING;
ERROR: column "n" does not exist

The parser should only allow the target table's column names and constants
in the index expression, as it does when creating the index. A variable
name is neither.

сб, 16 апр. 2016 г. в 1:04, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

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