Thread: plpgsql: ambiguous column reference in ON CONFLICT clause
Is there a way to prevent this from happening? I know I can use the PK constraint name or rename the OUT variable i. The question is can this be resolved while keeping the arbiter inference and the variable name.
CREATE TABLE x.x (
i INT PRIMARY KEY
);
CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING;
END
$$;
i INT PRIMARY KEY
);
CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING;
END
$$;
postgres=# select * from x.ins(1);
ERROR: column reference "i" is ambiguous
LINE 3: ON CONFLICT (i) DO NOTHING
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING
CONTEXT: PL/pgSQL function x.ins(integer) line 3 at SQL statement
ERROR: column reference "i" is ambiguous
LINE 3: ON CONFLICT (i) DO NOTHING
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING
CONTEXT: PL/pgSQL function x.ins(integer) line 3 at SQL statement
The conflicting variable is the OUT parameter of the function.
Normally, I'd suggest to fully qualify the name but the following or similar is a syntax error:
INSERT INTO x.x(i) AS t
SELECT p_i
ON CONFLICT (t.i) DO NOTHING;
SELECT p_i
ON CONFLICT (t.i) DO NOTHING;
According to the documentation in https://www.postgresql.org/docs/current/plpgsql-implementation.html:
> Query parameters will only be substituted in places where they are syntactically permissible.
and
> Another way to understand this is that variable substitution can only insert data values into an SQL command; it cannot dynamically change which database objects are referenced by the command.
After reading this I am wondering if the current behavior is actually a bug.
Thanks,
Torsten
On 2/6/23 09:46, Torsten Förtsch wrote: > Is there a way to prevent this from happening? I know I can use the PK > constraint name or rename the OUT variable i. The question is can this > be resolved while keeping the arbiter inference and the variable name. > > CREATE TABLE x.x ( > i INT PRIMARY KEY > ); > > CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT) > LANGUAGE plpgsql AS $$ > BEGIN > INSERT INTO x.x(i) > SELECT p_i > ON CONFLICT (i) DO NOTHING; > END > $$; > > postgres=# select * from x.ins(1); > ERROR: column reference "i" is ambiguous > LINE 3: ON CONFLICT (i) DO NOTHING > ^ > DETAIL: It could refer to either a PL/pgSQL variable or a table column. > QUERY: INSERT INTO x.x(i) > SELECT p_i > ON CONFLICT (i) DO NOTHING > CONTEXT: PL/pgSQL function x.ins(integer) line 3 at SQL statement > > The conflicting variable is the OUT parameter of the function. > > Normally, I'd suggest to fully qualify the name but the following or > similar is a syntax error: > > INSERT INTO x.x(i) AS t > SELECT p_i > ON CONFLICT (t.i) DO NOTHING; > > According to the documentation in > https://www.postgresql.org/docs/current/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/plpgsql-implementation.html>: > > > Query parameters will only be substituted in places where they are > syntactically permissible. See: https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST for your choices. > > and > > > Another way to understand this is that variable substitution can only > insert data values into an SQL command; it cannot dynamically change > which database objects are referenced by the command. > > After reading this I am wondering if the current behavior is actually a bug. > > Thanks, > Torsten -- Adrian Klaver adrian.klaver@aklaver.com
On Monday, February 6, 2023, Torsten Förtsch <tfoertsch123@gmail.com> wrote:
After reading this I am wondering if the current behavior is actually a bug.
Arguably it is a bug, and a known one at that if you want some light reading, but regardless there is presently no proposal to get rid of the POLA violation and little chance for any solution to be back-patched since the issue is lack of good existing technical options.
David J.
po 6. 2. 2023 v 19:02 odesílatel David G. Johnston <david.g.johnston@gmail.com> napsal:
On Monday, February 6, 2023, Torsten Förtsch <tfoertsch123@gmail.com> wrote:After reading this I am wondering if the current behavior is actually a bug.Arguably it is a bug, and a known one at that if you want some light reading, but regardless there is presently no proposal to get rid of the POLA violation and little chance for any solution to be back-patched since the issue is lack of good existing technical options.
This is an interesting issue. I fully agree, so it is very pretty unintuitive, but when you start calculating with possibility of expression indexes, it is probably the best that is possible. I don't think it is a bug. Unfortunately, at this moment we have no information about single or double braces usage. So there is not any possibility to say so there should not be an expression's index. And if there can be a functional index, then using a possible variable can be really confusing without unique meaning.
This is probably only one case, where you can specify an index in a query, so there are not any other similar cases, and this special case should be documented and explained in plpgsql doc.
Regards
Pavel
David J.