Re: Bug report: variable_conflict + ON CONFLICT

On Wed, Jan 5, 2022 at 6:27 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alexi Theodore <alexitheodore(at)gmail(dot)com> writes:
> > I'd like to report what I think is a bug. I've put together a script
> which highlights things pretty clearly. The short synopsis is that when
> using "#variable_conflict use_variable" in a function or procedure, the
> correct choice of variable vs column name is done everywhere (that I know
> of) except in the ON CONFLICT (<column name>) part of an INSERT statement.
> That one part only seems to not follow the conflict resolution pattern.
>
> The names in ON CONFLICT are not values, and it would not make
> sense to substitute plpgsql variable values for them. A related
> example is that if you write INSERT INTO t (a,b) VALUES (1,2),
> none of t, a, or b are candidates to be replaced by plpgsql
> variables.
>

Then why is the 42P10 ERROR happening here? We don't get to see what the
"ON CONFLICT specification" is in the error message though changing the
call argument to 'test_column' doesn't remove the error (I truly expected
that it would). Additionally, changing the resolution to "error" indeed
causes an error which contradicts the claim that nothing in the presented
INSERT command is ambiguous. And, if you specify "use_column" (which was
the advice given a few weeks ago [1]) the query executes successfully.
Omission of the directive results in an ambiguity error - so while I agree
it doesn't make sense to substitute plpgsql variable values there the
system is still trying (and failing to produce an expected result given its
attempt and the correct column name is provided).

The interpretation of the ON CONFLICT clause in pl/pgsql does not abide by
the expectations established for everything else. It boils down to the
fact that the "ON CONFLICT (column_name)" syntax is not correctly
identified as requiring an identifier. If it did then neither the
#variable_conflict nor "((table_name.column_name))" solutions provided in
[1] would be required - the simple and expected command would just work.

On a related question, for the syntax "ON CONFLICT ((index_expression))" is
index_expression something that can be parameterized. I've been assuming
so because I see nothing else about the ON CONFLICT clause that would need
parameters (and thus end up interpreted by pl/pgsql as being a place where
a variable makes sense). I am assuming we'd be able to describe the WHERE
clause part of the clause separately.

David J.

1.
https://www.postgresql.org/message-id/flat/61A7CB18.20091.3401B246%40Jenda.Krynicky.cz