Обсуждение: Bug report: variable_conflict + ON CONFLICT
Hi, I'd like to report what I think is a bug. I've put together a script which highlights things pretty clearly. The short synopsisis that when using "#variable_conflict use_variable" in a function or procedure, the correct choice of variable vscolumn 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. Thanks, Alex
Вложения
Alexi Theodore <alexitheodore@gmail.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
shortsynopsis is that when using "#variable_conflict use_variable" in a function or procedure, the correct choice of
variablevs 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.
regards, tom lane
On Wed, Jan 5, 2022 at 6:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alexi Theodore <alexitheodore@gmail.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.
I think that is my point though? Since the ON CONFLICT parameters are not variables, it should never cause a concern...
butin this case, thats exactly what is happening. Its almost being interpreted as though the variable is being replaced
withits value. I.e. ON CONFLICT ('awe') which of course is not going to work. The way it should work should be
identicalto other parts of the query which are the same condition and don't concern with whether to interpret as
variableor column, because they can only be column.
> On Jan 5, 2022, at 5:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Alexi Theodore <alexitheodore@gmail.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
shortsynopsis is that when using "#variable_conflict use_variable" in a function or procedure, the correct choice of
variablevs 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.
>
> regards, tom lane