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

Поиск
Список
Период
Сортировка
Искать

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

От:
Atisss hh <125atishprak@gmail.com>
Дата:
Вложения:


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.


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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Atisss hh <125atishprak@gmail.com> writes:
> On Sat, 7 Nov 2026 at 20:46, PG Bug reporting form 
> 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

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);

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

От:
Atisss hh <125atishprak@gmail.com>
Дата:
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

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

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
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


FAQ