pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) 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);

view thread (5+ messages)  latest in thread

Message-ID: <3773206.1794453200@sss.pgh.pa.us>
Permalink:  ../3773206.1794453200@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3773206.1794453200@sss.pgh.pa.us

 ·  · 

reply

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