Re: BUG #14089: ON CONFLICT allows function variables in index expressions - Mailing list pgsql-bugs

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