BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

Started by PG Bug reporting form15 days ago5 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 19848
Logged by: Atish Prakash
Email address: 125atishprak@gmail.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:

SQL-standard function bodies (BEGIN ATOMIC) handle parameter names wrongly
when
a function has OUT parameters before an input parameter, or an unnamed input
alongside named parameters. There are three visible symptoms.

1. A dump cannot be restored. An unnamed input with named OUT or TABLE
columns
deparses to an empty identifier:

CREATE TABLE s(a int PRIMARY KEY);
CREATE FUNCTION fa(int) RETURNS TABLE(x int) LANGUAGE sql
BEGIN ATOMIC SELECT a FROM s WHERE a = $1; END;
SELECT pg_get_functiondef('fa'::regproc);
-- ... WHERE (s.a = fa."");

Restoring pg_dump output gives:

ERROR: zero-length delimited identifier at or near """"

and the function is missing afterwards. These fail the same way:
f(int, OUT y int) and f(int, named int).

pg_upgrade is blocked too. With the function above in the old cluster,
pg_upgrade from 18.6 to 19rc1 stops at "Restoring database schemas in the
new
cluster" with the same error (exit status 1). The old server does the
deparsing, so a fix also needs to reach the old major version.

2. A valid function is rejected, and an OUT name is bound to the wrong
input.
Names are looked up by position among all parameters, including OUT ones:

CREATE FUNCTION a2(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT x + y; END;
-- ERROR: column "y" does not exist

CREATE FUNCTION a1(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT total; END;
SELECT a1(1, 2);
-- returns 2: "total" silently means input y

With AS $$ ... $$ the same bodies behave correctly: x + y works, and
total
gives 'column "total" does not exist'.

3. Deparsing shows the wrong name:

CREATE FUNCTION p1(a int, OUT b int, c int) LANGUAGE sql
BEGIN ATOMIC SELECT $1 * 10 + $2; END;
SELECT pg_get_function_sqlbody('p1'::regproc);
-- SELECT ((a * 10) + b) expected (a * 10) + c

Cause:
- interpret_function_parameter_list() appends every parameter to
inParameterNames_list, including OUT ones, although the list is then
indexed
by input position.
- print_function_sqlbody() passes the names from get_func_arg_info(), which
include OUT parameters, and get_parameter() indexes them by paramid. It
also
treats an empty name as a real name.

A possible patch is attached (0001-fix-sql-function-parameter-names.patch).
With
it, the cases above create, run and restore correctly on master, and all 239
core regression tests pass. It adds no new test.

Related, and not fixed by the patch: a parameter deparsed as function.name
can be
captured by a table of the same name, which changes results after a restore:

CREATE TABLE q2(a int);
INSERT INTO q2 VALUES (111);
CREATE FUNCTION q2(a int) RETURNS int LANGUAGE sql
BEGIN ATOMIC SELECT $1 FROM q2; END;
SELECT q2(5); -- 5 before dump and restore, 111 after

Found with dump and restore differential tests. I reproduced and checked the
result myself.

#2Atisss hh
125atishprak@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

On Sat, 7 Nov 2026 at 20:46, PG Bug reporting form <noreply@postgresql.org>
wrote:

Show quoted text

The following bug has been logged on the website:

Bug reference: 19848
Logged by: Atish Prakash
Email address: 125atishprak@gmail.com
PostgreSQL version: 18.6
Operating system: Linux x86_64
Description:

SQL-standard function bodies (BEGIN ATOMIC) handle parameter names wrongly
when
a function has OUT parameters before an input parameter, or an unnamed
input
alongside named parameters. There are three visible symptoms.

1. A dump cannot be restored. An unnamed input with named OUT or TABLE
columns
deparses to an empty identifier:

CREATE TABLE s(a int PRIMARY KEY);
CREATE FUNCTION fa(int) RETURNS TABLE(x int) LANGUAGE sql
BEGIN ATOMIC SELECT a FROM s WHERE a = $1; END;
SELECT pg_get_functiondef('fa'::regproc);
-- ... WHERE (s.a = fa."");

Restoring pg_dump output gives:

ERROR: zero-length delimited identifier at or near """"

and the function is missing afterwards. These fail the same way:
f(int, OUT y int) and f(int, named int).

pg_upgrade is blocked too. With the function above in the old cluster,
pg_upgrade from 18.6 to 19rc1 stops at "Restoring database schemas in
the
new
cluster" with the same error (exit status 1). The old server does the
deparsing, so a fix also needs to reach the old major version.

2. A valid function is rejected, and an OUT name is bound to the wrong
input.
Names are looked up by position among all parameters, including OUT
ones:

CREATE FUNCTION a2(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT x + y; END;
-- ERROR: column "y" does not exist

CREATE FUNCTION a1(x int, OUT total int, y int) LANGUAGE sql
BEGIN ATOMIC SELECT total; END;
SELECT a1(1, 2);
-- returns 2: "total" silently means input y

With AS $$ ... $$ the same bodies behave correctly: x + y works, and
total
gives 'column "total" does not exist'.

3. Deparsing shows the wrong name:

CREATE FUNCTION p1(a int, OUT b int, c int) LANGUAGE sql
BEGIN ATOMIC SELECT $1 * 10 + $2; END;
SELECT pg_get_function_sqlbody('p1'::regproc);
-- SELECT ((a * 10) + b) expected (a * 10) + c

Cause:
- interpret_function_parameter_list() appends every parameter to
inParameterNames_list, including OUT ones, although the list is then
indexed
by input position.
- print_function_sqlbody() passes the names from get_func_arg_info(), which
include OUT parameters, and get_parameter() indexes them by paramid. It
also
treats an empty name as a real name.

A possible patch is attached (0001-fix-sql-function-parameter-names.patch).
With
it, the cases above create, run and restore correctly on master, and all
239
core regression tests pass. It adds no new test.

Related, and not fixed by the patch: a parameter deparsed as function.name
can be
captured by a table of the same name, which changes results after a
restore:

CREATE TABLE q2(a int);
INSERT INTO q2 VALUES (111);
CREATE FUNCTION q2(a int) RETURNS int LANGUAGE sql
BEGIN ATOMIC SELECT $1 FROM q2; END;
SELECT q2(5); -- 5 before dump and restore, 111 after

Found with dump and restore differential tests. I reproduced and checked
the
result myself.

Attachments:

t254599_1
0001-fix-sql-function-parameter-names.patchtext/x-patch; charset=US-ASCII; name=0001-fix-sql-function-parameter-names.patchDownload+21-2
#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Atisss hh (#2)
Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

Atisss hh <125atishprak@gmail.com> writes:

On Sat, 7 Nov 2026 at 20:46, PG Bug reporting form <noreply@postgresql.org>
wrote:

SQL-standard function bodies (BEGIN ATOMIC) handle parameter names
wrongly when a function has OUT parameters before an input
parameter, or an unnamed input alongside named parameters.

Good catch --- obviously we've not tested the case with OUT parameters
written first very much :-(

A possible patch is attached (0001-fix-sql-function-parameter-names.patch).

I don't like the details of this patch, though.
interpret_function_parameter_list can be fixed more simply by moving
the erroneous code, and the real problem with print_function_sqlbody
is that it's using the wrong support function, and all of this is
a bit underdocumented.

I also looked around at other callers of get_func_arg_info, and
noted that PL/Python is a little bit broken and plsample.c is a
whole lot broken --- fortunately that's just test code.

Related, and not fixed by the patch: a parameter deparsed as
function.name can be captured by a table of the same name, which
changes results after a restore:

CREATE TABLE q2(a int);
INSERT INTO q2 VALUES (111);
CREATE FUNCTION q2(a int) RETURNS int LANGUAGE sql
BEGIN ATOMIC SELECT $1 FROM q2; END;
SELECT q2(5); -- 5 before dump and restore, 111 after

Hmm. That's not related really, it's a scoping problem.
The function deparses to

CREATE OR REPLACE FUNCTION public.q2(a integer)
RETURNS integer
LANGUAGE sql
BEGIN ATOMIC
SELECT q2.a
FROM q2;
END;

I think that interpreting q2.a as the table's column is fully correct
here: the query's FROM clause is the most closely nested referent.
So the real problem is that we're emitting the function parameter
reference in a form that is fundamentally ambiguous, and that we do
not have a way to make non-ambiguous. The only simple way out is to
always print new-style-SQL-function-body function parameter references
as $n not as names, which feels pretty sad. On the bright side, we
could rip out a few dozen lines of buggy-anyway code in ruleutils.c.
Anyone have a better idea?

regards, tom lane

Attachments:

t254599_2
0002-fix-sql-function-parameter-names.patchtext/x-diff; charset=us-ascii; name=0002-fix-sql-function-parameter-names.patchDownload+152-21
#4Atisss hh
125atishprak@gmail.com
In reply to: Tom Lane (#3)
Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

Hi Tom,

Sorry for the late reply, and thanks for the fix and for checking the
other callers of get_func_arg_info.

I tested your patch on current master. All the cases from my report now
create, dump and restore with identical results, and the core regression
tests (243) and plsample pass. pg_upgrade from a patched build to a patched
build works. Upgrading from 18.6 still fails with the empty identifier,
since the old server does the deparsing, so this would also need
backpatching to 18 to help pg_upgrade from there.

Agreed that the q2 case is a scoping problem and not part of this bug.
I don't have a better idea than printing $n, since any qualified name
can be captured by a FROM item with the same name.

Regards,
Atish

On Thu, 12 Nov 2026 at 08:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Atisss hh <125atishprak@gmail.com> writes:

On Sat, 7 Nov 2026 at 20:46, PG Bug reporting form <

noreply@postgresql.org>

wrote:

SQL-standard function bodies (BEGIN ATOMIC) handle parameter names
wrongly when a function has OUT parameters before an input
parameter, or an unnamed input alongside named parameters.

Good catch --- obviously we've not tested the case with OUT parameters
written first very much :-(

A possible patch is attached

(0001-fix-sql-function-parameter-names.patch).

I don't like the details of this patch, though.
interpret_function_parameter_list can be fixed more simply by moving
the erroneous code, and the real problem with print_function_sqlbody
is that it's using the wrong support function, and all of this is
a bit underdocumented.

I also looked around at other callers of get_func_arg_info, and
noted that PL/Python is a little bit broken and plsample.c is a
whole lot broken --- fortunately that's just test code.

Related, and not fixed by the patch: a parameter deparsed as
function.name can be captured by a table of the same name, which
changes results after a restore:

CREATE TABLE q2(a int);
INSERT INTO q2 VALUES (111);
CREATE FUNCTION q2(a int) RETURNS int LANGUAGE sql
BEGIN ATOMIC SELECT $1 FROM q2; END;
SELECT q2(5); -- 5 before dump and restore, 111 after

Hmm. That's not related really, it's a scoping problem.
The function deparses to

CREATE OR REPLACE FUNCTION public.q2(a integer)
RETURNS integer
LANGUAGE sql
BEGIN ATOMIC
SELECT q2.a
FROM q2;
END;

I think that interpreting q2.a as the table's column is fully correct
here: the query's FROM clause is the most closely nested referent.
So the real problem is that we're emitting the function parameter
reference in a form that is fundamentally ambiguous, and that we do
not have a way to make non-ambiguous. The only simple way out is to
always print new-style-SQL-function-body function parameter references
as $n not as names, which feels pretty sad. On the bright side, we
could rip out a few dozen lines of buggy-anyway code in ruleutils.c.
Anyone have a better idea?

regards, tom lane

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Atisss hh (#4)
Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade

Atisss hh <125atishprak@gmail.com> writes:

I tested your patch on current master. All the cases from my report now
create, dump and restore with identical results, and the core regression
tests (243) and plsample pass. pg_upgrade from a patched build to a patched
build works. Upgrading from 18.6 still fails with the empty identifier,
since the old server does the deparsing, so this would also need
backpatching to 18 to help pg_upgrade from there.

Yeah, it needs back-patching. I did that and pushed it.

Agreed that the q2 case is a scoping problem and not part of this bug.
I don't have a better idea than printing $n, since any qualified name
can be captured by a FROM item with the same name.

I'll wait to see if Peter has a better idea before doing anything
about that part. The problem would only arise with a function name
that matches a table alias in some query within the function;
which is certainly possible but it must not be common, else we'd
have heard about this years ago.

regards, tom lane