pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug report: variable_conflict + ON CONFLICT
4+ messages / 3 participants
[nested] [flat]
* Bug report: variable_conflict + ON CONFLICT
@ 2022-01-06 00:40 Alexi Theodore <alexitheodore@gmail.com>
2022-01-06 01:27 ` Re: Bug report: variable_conflict + ON CONFLICT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Alexi Theodore @ 2022-01-06 00:40 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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 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.
Thanks,
Alex
Attachments:
bug report - ON CONFLICT and variable_conflict.txt (text/plain, 1.1K) download
ROLLBACK; BEGIN;
CREATE TABLE example_table (
test_column TEXT unique
)
;
INSERT INTO example_table VALUES ('abc')
ON CONFLICT (test_column) DO NOTHING
;
CREATE PROCEDURE example_works (
test_column_in TEXT
) AS
$$
#variable_conflict use_variable
BEGIN
INSERT INTO example_table (test_column) VALUES (test_column_in)
ON CONFLICT (test_column) DO NOTHING
;
END
$$
LANGUAGE plpgsql
;
CALL example_works('xyz');
SELECT * FROM example_table
;
CREATE PROCEDURE example_breaks (
test_column TEXT
-- ^ note: the new declaration name is the same as the column name
) AS
$$
#variable_conflict use_variable
BEGIN
-- ^ normally, name conflicts would be resolved successully this way...
INSERT INTO example_table (test_column) -- `variable_conflict` is working here √
VALUES (test_column) -- `variable_conflict` is working here √
ON CONFLICT (test_column) DO NOTHING -- `variable_conflict` is NOT working here X @ `(test_column)`
;
END
$$
LANGUAGE plpgsql
;
CALL example_breaks('qwe');
/*
[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
PL/pgSQL function example_breaks(text) line 6 at SQL statement
*/
ROLLBACK;=
* Re: Bug report: variable_conflict + ON CONFLICT
2022-01-06 00:40 Bug report: variable_conflict + ON CONFLICT Alexi Theodore <alexitheodore@gmail.com>
@ 2022-01-06 01:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-06 02:22 ` Re: Bug report: variable_conflict + ON CONFLICT David G. Johnston <david.g.johnston@gmail.com>
2022-01-06 02:48 ` Re: Bug report: variable_conflict + ON CONFLICT Alexi Theodore <alexitheodore@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2022-01-06 01:27 UTC (permalink / raw)
To: Alexi Theodore <alexitheodore@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug report: variable_conflict + ON CONFLICT
2022-01-06 00:40 Bug report: variable_conflict + ON CONFLICT Alexi Theodore <alexitheodore@gmail.com>
2022-01-06 01:27 ` Re: Bug report: variable_conflict + ON CONFLICT Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-01-06 02:22 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2022-01-06 02:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alexi Theodore <alexitheodore@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
1.
https://www.postgresql.org/message-id/flat/61A7CB18.20091.3401B246%40Jenda.Krynicky.cz
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug report: variable_conflict + ON CONFLICT
2022-01-06 00:40 Bug report: variable_conflict + ON CONFLICT Alexi Theodore <alexitheodore@gmail.com>
2022-01-06 01:27 ` Re: Bug report: variable_conflict + ON CONFLICT Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-01-06 02:48 ` Alexi Theodore <alexitheodore@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Alexi Theodore @ 2022-01-06 02:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
I think that is my point though? Since the ON CONFLICT parameters are not variables, it should never cause a concern... but in this case, thats exactly what is happening. Its almost being interpreted as though the variable is being replaced with its value. I.e. ON CONFLICT ('awe') which of course is not going to work. The way it should work should be identical to other parts of the query which are the same condition and don't concern with whether to interpret as variable or 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 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.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-01-06 02:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-06 00:40 Bug report: variable_conflict + ON CONFLICT Alexi Theodore <alexitheodore@gmail.com>
2022-01-06 01:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-01-06 02:22 ` David G. Johnston <david.g.johnston@gmail.com>
2022-01-06 02:48 ` Alexi Theodore <alexitheodore@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox