pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-11-12 c6e70d5d37 64e8d6f894 c87192d13d 4343c135e7 0f351673b5 8e27c9261c Fix assorted confusion about parameter names.
* BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
@ 2026-11-07 15:15 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2026-11-07 15:15 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 125atishprak@gmail.com
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
@ 2026-11-07 15:16 Atisss hh <125atishprak@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Atisss hh @ 2026-11-07 15:16 UTC (permalink / raw)
To: 125atishprak@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, 7 Nov 2026 at 20:46, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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:
0001-fix-sql-function-parameter-names.patch (text/x-patch, 1.7K) download
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 4d46013d7d..2bc2fc4c83 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -418,7 +418,7 @@ interpret_function_parameter_list(ParseState *pstate,
have_names = true;
}
- if (inParameterNames_list)
+ if (isinput && inParameterNames_list)
*inParameterNames_list = lappend(*inParameterNames_list, makeString(fp->name ? fp->name : pstrdup("")));
if (fp->defexpr)
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 8f6a46fa61..f6d6b3d428 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3572,6 +3572,24 @@ print_function_sqlbody(StringInfo buf, HeapTuple proctup)
dpns.funcname = pstrdup(NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname));
numargs = get_func_arg_info(proctup,
&argtypes, &argnames, &argmodes);
+
+ /*
+ * Param IDs count input parameters only, so remove the names of output
+ * parameters to keep the array indexable by paramid.
+ */
+ if (argnames && argmodes)
+ {
+ int ninput = 0;
+
+ for (int i = 0; i < numargs; i++)
+ {
+ if (argmodes[i] != PROARGMODE_OUT &&
+ argmodes[i] != PROARGMODE_TABLE)
+ argnames[ninput++] = argnames[i];
+ }
+ numargs = ninput;
+ }
+
dpns.numargs = numargs;
dpns.argnames = argnames;
@@ -8815,7 +8833,8 @@ get_parameter(Param *param, deparse_context *context)
{
char *argname = dpns->argnames[param->paramid - 1];
- if (argname)
+ /* An unnamed parameter has an empty name if others are named */
+ if (argname && argname[0] != '\0')
{
bool should_qualify = false;
ListCell *lc;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
@ 2026-11-12 03:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Atisss hh <125atishprak@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-11-12 03:13 UTC (permalink / raw)
To: Atisss hh <125atishprak@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
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:
0002-fix-sql-function-parameter-names.patch (text/x-diff, 12.7K) download
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 4d46013d7de..99c6128c0f8 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -322,6 +322,9 @@ interpret_function_parameter_list(ParseState *pstate,
isinput = true;
if (parameterTypes_list)
*parameterTypes_list = lappend_oid(*parameterTypes_list, toid);
+ if (inParameterNames_list)
+ *inParameterNames_list = lappend(*inParameterNames_list,
+ makeString(fp->name ? fp->name : pstrdup("")));
}
/* handle output parameters */
@@ -418,9 +421,6 @@ interpret_function_parameter_list(ParseState *pstate,
have_names = true;
}
- if (inParameterNames_list)
- *inParameterNames_list = lappend(*inParameterNames_list, makeString(fp->name ? fp->name : pstrdup("")));
-
if (fp->defexpr)
{
Node *def;
@@ -930,9 +930,10 @@ interpret_AS_clause(Oid languageOid, const char *languageName,
pinfo->fname = funcname;
pinfo->nargs = list_length(parameterTypes);
+ Assert(pinfo->nargs == list_length(inParameterNames));
pinfo->argtypes = palloc_array(Oid, pinfo->nargs);
pinfo->argnames = palloc_array(char *, pinfo->nargs);
- for (int i = 0; i < list_length(parameterTypes); i++)
+ for (int i = 0; i < pinfo->nargs; i++)
{
char *s = strVal(list_nth(inParameterNames, i));
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 8f6a46fa614..e9c1c9db1b1 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -182,8 +182,8 @@ typedef struct
List *index_tlist; /* referent for INDEX_VAR Vars */
/* Special namespace representing a function signature: */
char *funcname;
- int numargs;
- char **argnames;
+ int numargs; /* number of input arguments */
+ char **argnames; /* names of input args (NULL if unnamed) */
} deparse_namespace;
/*
@@ -3561,19 +3561,29 @@ pg_get_function_arg_default(PG_FUNCTION_ARGS)
static void
print_function_sqlbody(StringInfo buf, HeapTuple proctup)
{
- int numargs;
- Oid *argtypes;
- char **argnames;
- char *argmodes;
+ Datum proargnames;
+ Datum proargmodes;
+ bool isNull;
deparse_namespace dpns = {0};
Datum tmp;
Node *n;
dpns.funcname = pstrdup(NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname));
- numargs = get_func_arg_info(proctup,
- &argtypes, &argnames, &argmodes);
- dpns.numargs = numargs;
- dpns.argnames = argnames;
+
+ proargnames = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargnames,
+ &isNull);
+ if (isNull)
+ proargnames = PointerGetDatum(NULL); /* just to be sure */
+
+ proargmodes = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargmodes,
+ &isNull);
+ if (isNull)
+ proargmodes = PointerGetDatum(NULL); /* just to be sure */
+
+ dpns.numargs = get_func_input_arg_names(proargnames, proargmodes,
+ &dpns.argnames);
tmp = SysCacheGetAttrNotNull(PROCOID, proctup, Anum_pg_proc_prosqlbody);
n = stringToNode(TextDatumGetCString(tmp));
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
index 5c56002ba0e..5cc6c52d36d 100644
--- a/src/backend/utils/fmgr/funcapi.c
+++ b/src/backend/utils/fmgr/funcapi.c
@@ -1375,6 +1375,9 @@ get_type_func_class(Oid typid, Oid *base_typeid)
* *p_argnames and *p_argmodes will be set NULL in the default cases
* (no names, and all IN arguments, respectively).
*
+ * If there are both named and unnamed arguments, p_argnames[] will
+ * contain empty strings (not NULLs) for the unnamed ones.
+ *
* Note that this function simply fetches what is in the pg_proc tuple;
* it doesn't do any interpretation of polymorphic types.
*/
@@ -1519,7 +1522,8 @@ get_func_trftypes(HeapTuple procTup,
*
* Returns the number of input arguments, which is the length of the
* palloc'd array returned to *arg_names. Entries for unnamed args
- * are set to NULL. You don't get anything if proargnames is NULL.
+ * are set to NULL (note the inconsistency with get_func_arg_info!).
+ * You don't get anything if proargnames is NULL.
*/
int
get_func_input_arg_names(Datum proargnames, Datum proargmodes,
diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c
index ce8b1e099e9..c28ef2fa626 100644
--- a/src/pl/plpython/plpy_procedure.c
+++ b/src/pl/plpython/plpy_procedure.c
@@ -289,7 +289,8 @@ PLy_procedure_create(PLyProcedure *proc,
proc);
/* get argument name */
- proc->argnames[pos] = names ? pstrdup(names[i]) : NULL;
+ proc->argnames[pos] =
+ (names && names[i][0] != '\0') ? pstrdup(names[i]) : NULL;
pos++;
}
diff --git a/src/test/modules/plsample/plsample.c b/src/test/modules/plsample/plsample.c
index a4d95969802..605ad1659c9 100644
--- a/src/test/modules/plsample/plsample.c
+++ b/src/test/modules/plsample/plsample.c
@@ -111,6 +111,7 @@ plsample_func_handler(PG_FUNCTION_ARGS)
Oid prorettype;
FmgrInfo result_in_func;
int numargs;
+ int inargno;
/* Fetch the function's pg_proc entry. */
pl_tuple = SearchSysCache1(PROCOID,
@@ -149,23 +150,34 @@ plsample_func_handler(PG_FUNCTION_ARGS)
* Iterate through all of the function arguments, printing each input
* value.
*/
+ inargno = 0;
for (int i = 0; i < numargs; i++)
{
- Oid argtype = pl_struct->proargtypes.values[i];
+ Oid argtype = argtypes[i];
char *value;
+ if (argmodes &&
+ (argmodes[i] == PROARGMODE_OUT ||
+ argmodes[i] == PROARGMODE_TABLE))
+ continue; /* skip OUT arguments */
+
type_tuple = SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype));
if (!HeapTupleIsValid(type_tuple))
elog(ERROR, "cache lookup failed for type %u", argtype);
type_struct = (Form_pg_type) GETSTRUCT(type_tuple);
- fmgr_info_cxt(type_struct->typoutput, &(arg_out_func[i]), proc_cxt);
+ fmgr_info_cxt(type_struct->typoutput,
+ &(arg_out_func[inargno]), proc_cxt);
ReleaseSysCache(type_tuple);
- value = OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value);
+ value = OutputFunctionCall(&arg_out_func[inargno],
+ fcinfo->args[inargno].value);
ereport(NOTICE,
(errmsg("argument: %d; name: %s; value: %s",
- i, argnames[i], value)));
+ inargno,
+ (argnames && argnames[i][0]) ? argnames[i] : "(none)",
+ value)));
+ inargno++;
}
/* Type of the result */
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index df7188d3de2..57a86927047 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -485,6 +485,73 @@ SELECT pg_get_functiondef('functest_S_16'::regproc);
DROP TABLE functest1 CASCADE;
NOTICE: drop cascades to function functest_s_16(integer,integer)
+-- check cases with OUT parameters before IN ones
+CREATE FUNCTION functest_out_point(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(x,y); $$;
+SELECT functest_out_point(11,22);
+ functest_out_point
+--------------------
+ (11,22)
+(1 row)
+
+CREATE FUNCTION functest_out_point2(OUT p point, float8, y float8)
+LANGUAGE sql AS $$ select point($1,y); $$;
+SELECT functest_out_point2(11,22);
+ functest_out_point2
+---------------------
+ (11,22)
+(1 row)
+
+CREATE FUNCTION functest_out_point_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(x,y); END;
+SELECT functest_out_point_sql(11,22);
+ functest_out_point_sql
+------------------------
+ (11,22)
+(1 row)
+
+SELECT pg_get_functiondef('functest_out_point_sql'::regproc);
+ pg_get_functiondef
+-----------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_out_point_sql(OUT p point, x double precision, y double precision)+
+ RETURNS point +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT point(x, y) AS point; +
+ END +
+
+(1 row)
+
+CREATE FUNCTION functest_out_point2_sql(OUT p point, float8, y float8)
+BEGIN ATOMIC SELECT point($1,y); END;
+SELECT functest_out_point2_sql(11,22);
+ functest_out_point2_sql
+-------------------------
+ (11,22)
+(1 row)
+
+SELECT pg_get_functiondef('functest_out_point2_sql'::regproc);
+ pg_get_functiondef
+----------------------------------------------------------------------------------------------------------------------
+ CREATE OR REPLACE FUNCTION temp_func_test.functest_out_point2_sql(OUT p point, double precision, y double precision)+
+ RETURNS point +
+ LANGUAGE sql +
+ BEGIN ATOMIC +
+ SELECT point($1, y) AS point; +
+ END +
+
+(1 row)
+
+CREATE FUNCTION functest_out_error(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(p,x); $$;
+ERROR: column "p" does not exist
+LINE 2: LANGUAGE sql AS $$ select point(p,x); $$;
+ ^
+CREATE FUNCTION functest_out_error_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(p,x); END;
+ERROR: column "p" does not exist
+LINE 2: BEGIN ATOMIC SELECT point(p,x); END;
+ ^
-- test with views
CREATE TABLE functest3 (a int);
INSERT INTO functest3 VALUES (1), (2);
@@ -846,7 +913,7 @@ CONTEXT: SQL function "test1" during startup
RESET check_function_bodies;
-- Cleanup
DROP SCHEMA temp_func_test CASCADE;
-NOTICE: drop cascades to 41 other objects
+NOTICE: drop cascades to 45 other objects
DETAIL: drop cascades to function functest_a_1(text,date)
drop cascades to function functest_a_2(text[])
drop cascades to function functest_a_3()
@@ -872,6 +939,10 @@ drop cascades to function functest_s_3a()
drop cascades to function functest_s_10(text,date)
drop cascades to function functest_s_13()
drop cascades to function functest_s_15(integer)
+drop cascades to function functest_out_point(double precision,double precision)
+drop cascades to function functest_out_point2(double precision,double precision)
+drop cascades to function functest_out_point_sql(double precision,double precision)
+drop cascades to function functest_out_point2_sql(double precision,double precision)
drop cascades to function functest_b_2(bigint)
drop cascades to function functest_srf0()
drop cascades to function functest_sri1()
diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 4377c5b569c..4dd80bb8498 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -257,6 +257,38 @@ SELECT pg_get_functiondef('functest_S_16'::regproc);
DROP TABLE functest1 CASCADE;
+-- check cases with OUT parameters before IN ones
+
+CREATE FUNCTION functest_out_point(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(x,y); $$;
+
+SELECT functest_out_point(11,22);
+
+CREATE FUNCTION functest_out_point2(OUT p point, float8, y float8)
+LANGUAGE sql AS $$ select point($1,y); $$;
+
+SELECT functest_out_point2(11,22);
+
+CREATE FUNCTION functest_out_point_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(x,y); END;
+
+SELECT functest_out_point_sql(11,22);
+
+SELECT pg_get_functiondef('functest_out_point_sql'::regproc);
+
+CREATE FUNCTION functest_out_point2_sql(OUT p point, float8, y float8)
+BEGIN ATOMIC SELECT point($1,y); END;
+
+SELECT functest_out_point2_sql(11,22);
+
+SELECT pg_get_functiondef('functest_out_point2_sql'::regproc);
+
+CREATE FUNCTION functest_out_error(OUT p point, x float8, y float8)
+LANGUAGE sql AS $$ select point(p,x); $$;
+
+CREATE FUNCTION functest_out_error_sql(OUT p point, x float8, y float8)
+BEGIN ATOMIC SELECT point(p,x); END;
+
-- test with views
CREATE TABLE functest3 (a int);
INSERT INTO functest3 VALUES (1), (2);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
@ 2026-11-12 16:22 Atisss hh <125atishprak@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Atisss hh @ 2026-11-12 16:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
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:
> 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
@ 2026-11-12 19:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Atisss hh <125atishprak@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2026-11-12 19:56 UTC (permalink / raw)
To: Atisss hh <125atishprak@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-11-12 19:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-07 15:15 BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade PG Bug reporting form <noreply@postgresql.org>
2026-11-07 15:16 ` Atisss hh <125atishprak@gmail.com>
2026-11-12 03:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-11-12 16:22 ` Atisss hh <125atishprak@gmail.com>
2026-11-12 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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