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

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.

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) O=
N 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 (value=
,
> (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 CONFLI=
CT
> 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 variable=
s
> 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.

On Fri, Apr 15, 2016 at 2:36 PM,  <quassnoi@gmail.com> wrote:
> test=3D# INSERT INTO test (value) SELECT * FROM (VALUES (1)) q (n) ON CON=
FLICT
> (value, (n)) DO NOTHING;
> ERROR:  column "n" does not exist
> =D0=A1=D0=A2=D0=A0=D0=9E=D0=9A=D0=90 1: ...CT * FROM (VALUES (1)) q (n) O=
N CONFLICT (value, (n)) DO
> NOT...
> =D0=9F=D0=9E=D0=94=D0=A1=D0=9A=D0=90=D0=97=D0=9A=D0=90:  There is a colum=
n named "n" in table "*SELECT*", but it cannot
> be referenced from this part of the query.
>
> 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 (value,
> (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 CONFLI=
CT
> 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 variable=
s
> 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.

--=20
Peter Geoghegan

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

On Friday, April 15, 2016, Alex Bolenok <quassnoi@gmail.com> wrote:

> 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.
>
>
The parsed insert never see the letter "n".  Once you comprehend that fact
your statement is true - because of the word "constant".  That constant is
why it compiles.

David J.

On Mon, Apr 18, 2016 at 5:36 AM, Alex Bolenok <quassnoi@gmail.com> wrote:

> I realize there is an explanation to that (it's the way it's programmed,
> after all), and that might be not a bug worthy enough to fix (however,
> there are people actually affected by it, see here:
> http://stackoverflow.com/questions/36656643/is-variable-conflict-use-vari=
able-not-working-with-on-conflict-clause-of-upsert ),
> but it's clearly a bug in my opinion.
>

=E2=80=8BThat SO post is not convincing - though I don't know if the "tname=
" in the
ON CONFLICT clause can be somehow prefixed so that there is no conflict.
As written, there is a conflict between the table column and the function
parameter and, as instructed, PostgreSQL is using the variable which then
makes the ON CONFLICT clause - as executed - one that does not match
anything in the system.

I'm not particularly concerned with user-space code that uses that pragma.

Unless you are prepared to explain in great depth the hazards of the
current behavior, and probably more importantly the value in some
alternative behavior I'd suggest accepting the fact that this behavior is
precisely how functions work and move on.  Just screaming "its a bug" is
not getting us anywhere.

It is possible to want to write a function that parameterizes the predicate
portion of the ON CONFLICT clause - i.e., one that when called with the
appropriate argument will find a match and resolve properly.  Maybe someone
can provide an example that would work since right now I'm out of time.
So, your original premise is wrong one its face - while likely having
minimal practical use the function as written could succeed with the right
pre-conditions.

David J.

This would have made sense if fn_test(1) would not fail (the last statement
in my script), but it does fail. Somehow the variable is constant enough
for the statement to parse, but is not constant enough for it to run.

It just does not make any sense.

fn_test is an SQL function, not PL/pgSQL, it should see the variable in
place of a constant on the parsing stage and not compile at all.

I realize there is an explanation to that (it's the way it's programmed,
after all), and that might be not a bug worthy enough to fix (however,
there are people actually affected by it, see here:
http://stackoverflow.com/questions/36656643/is-variable-conflict-use-variab=
le-not-working-with-on-conflict-clause-of-upsert
),
but it's clearly a bug in my opinion.

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

> On Friday, April 15, 2016, Alex Bolenok <quassnoi@gmail.com> wrote:
>
>> 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
>> 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.
>>
>>
> The parsed insert never see the letter "n".  Once you comprehend that fac=
t
> your statement is true - because of the word "constant".  That constant i=
s
> why it compiles.
>
> David J.
>

On Mon, Apr 18, 2016 at 5:36 AM, Alex Bolenok <quassnoi@gmail.com> wrote:
> This would have made sense if fn_test(1) would not fail (the last stateme=
nt
> in my script), but it does fail. Somehow the variable is constant enough =
for
> the statement to parse, but is not constant enough for it to run.

But it does run -- it just fails during inference (planning), rather
than during parse analysis (e.g. during parse analysis, when, as it
happens, there is no column that matches the one spelled out in the
inference clause). The reason inference/planning fails has nothing to
do with your original complaint, which is that it should consider the
SQL arg to be column-like, and fail earlier.

It now seems like you'd also think it okay it this statement did not
error at all:

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" d=
uring startup

You think that that other alternative behavior (not erroring out) is
at least equally good to erroring during parse analysis, because not
erroring out here (during inference) would at least be consistent with
what we've said about "n"/1 being a constant ("n" is actually not a
constant -- more on that later). Your problem is not so much that it
doesn't fail in exactly the same way as if "n" was a column. Your
exact complaint is that this shows an expression that isn't quite a
column-like thing, but also is not quite a constant-like thing (as
evidenced by this error message, which shows it almost-but-not-quite
working). No explanation of the behavior is self-consistent, so they
must all be wrong. Right?

If I have that right, then I see your point.

The reason this happens it isn't actually a constant in the parse tree
(I misspoke). While the first inference element in the parse tree is a
column/Var as expected, the second is actually a Param. This is
something that "set debug_print_parse =3D on" output shows:

"""
*** SNIP ***
  :onConflict
     {ONCONFLICTEXPR
     :action 1
     :arbiterElems (
        {INFERENCEELEM
        :expr
           {VAR
           :varno 1
           :varattno 2
           :vartype 23
           :vartypmod -1
           :varcollid 0
           :varlevelsup 0
           :varnoold 1
           :varoattno 2
           :location 49
           }
        :infercollid 0
        :inferopclass 0
        }
        {INFERENCEELEM
        :expr
           {PARAM
           :paramkind 0
           :paramid 1
           :paramtype 23
           :paramtypmod -1
           :paramcollid 0
           :location 58
           }
        :infercollid 0
        :inferopclass 0
        }
     )
     :arbiterWhere <>
     :constraint 0
     :onConflictSet <>
     :onConflictWhere <>
     :exclRelIndex 0
     :exclRelTlist <>
     }
  :returningList <>
  :groupClause <>
  :groupingSets <>
  :havingQual <>
  :windowClause <>
  :distinctClause <>
  :sortClause <>
  :limitOffset <>
  :limitCount <>
  :rowMarks <>
  :setOperations <>
  :constraintDeps <>
  }
CONTEXT:  SQL function "fn_test" during startup
STATEMENT:  select * from fn_test(1);
ERROR:  there is no unique or exclusion constraint matching the ON
CONFLICT specification
"""

So, I guess, in a round-about sort of way this *does* match your
preferred behavior. I don't think this needs any kind of fix. I
suppose it would be slightly better if the error said something along
the lines of "a parameter cannot appear in an index definition, so
your inference specification is self-evidently wrong", but this issue
is so esoteric that I see little point.

--=20
Peter Geoghegan