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=3D# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON CONFL=
ICT
(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.

=D1=81=D0=B1, 16 =D0=B0=D0=BF=D1=80. 2016 =D0=B3. =D0=B2 1:04, David G. Joh=
nston <david.g.johnston@gmail.com>:

> On Fri, Apr 15, 2016 at 2:36 PM, <quassnoi@gmail.com> wrote:
>
>> 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:
>>
>
> =E2=80=8B[snip]
>
>
>
>> =E2=80=8B
>>
>> test=3D# INSERT INTO test (value) VALUES (1) ON CONFLICT (value,
>> (no_such_column)) DO NOTHING;
>> ERROR:  column "no_such_column" does not exist
>> =D0=A1=D0=A2=D0=A0=D0=9E=D0=9A=D0=90 1: ...INTO test (value) VALUES (1) =
ON CONFLICT (value,
>> (no_such_co...
>>
>> =E2=80=8B[snip]
> =E2=80=8B
>
>
>> test=3D# DROP FUNCTION IF EXISTS fn_test(INT); CREATE FUNCTION fn_test(n
>> INT)
>> RETURNS VOID AS $$ INSERT INTO test (value) VALUES (1) ON CONFLICT (valu=
e,
>> (n)) DO NOTHING; $$ LANGUAGE 'sql';
>> DROP FUNCTION
>> CREATE FUNCTION
>>
>> test=3D# SELECT * FROM fn_test(1);
>> ERROR:  there is no unique or exclusion constraint matching the ON
>> CONFLICT
>> specification
>> =D0=9A=D0=9E=D0=9D=D0=A2=D0=95=D0=9A=D0=A1=D0=A2:  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 variabl=
es
>> 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.  Give=
n
> that both scenarios are, correctly, errors, the difference in messages ar=
e
> because of the execution environment and thus have every right to be
> different.
>
> David J.
>
>