From: Tom Lane <tgl@sss.pgh.pa.us>
To: Atisss hh <125atishprak@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Subject: Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
Date: Wed, 11 Nov 2026 22:13:20 -0500
Message-ID: <3773206.1794453200@sss.pgh.pa.us> (raw)
In-Reply-To: <CAGqfTtueQf=MRh8hC3z7D6iATXDFfrz2WDLpPLrbpUV5K5CrAw@mail.gmail.com>
References: <19848-c4d5bc38ad4ab8b8@postgresql.org>
<CAGqfTtueQf=MRh8hC3z7D6iATXDFfrz2WDLpPLrbpUV5K5CrAw@mail.gmail.com>
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) downloaddiff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.cindex 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.cindex 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.cindex 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.cindex 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.cindex 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.outindex 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.sqlindex 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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, 125atishprak@gmail.com, peter@eisentraut.org
Subject: Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
In-Reply-To: <3773206.1794453200@sss.pgh.pa.us>
References: <19848-c4d5bc38ad4ab8b8@postgresql.org> <CAGqfTtueQf=MRh8hC3z7D6iATXDFfrz2WDLpPLrbpUV5K5CrAw@mail.gmail.com> <3773206.1794453200@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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