Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRLFk-00000000t3U-16Na for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 03:13:32 +0000 Received: from localhost ([127.0.0.1]:41558 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRLFi-0000000EVQc-3Oak for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 03:13:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:53426) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRLFi-0000000EVQT-1Ecg for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 03:13:30 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRLFg-00000000TdI-1AHK for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 03:13:29 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 6AC3DKsQ3773207; Wed, 11 Nov 2026 22:13:20 -0500 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 6AC3DKsQ3773207 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1794453200; bh=Cehr6X1hSnA9oXgIkbiFvHaRoapdlq0ai+7tahuuCzM=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=NxjRHVh3vNbv12mPZqqdN4d55NKMbB+aULEdQ2hruW05HbRCy86Q0YQKB9JuSp3kk +oN2LmWGwEs2AXmfHT0gTFNBZtz/c7yION0oE+J4WDd6Y0zIvK1MT00KlDtYZHBKK9 IiHRNyHL7/7SVHJF6urOWgvEZCAP4NHs4WPG6w88= From: Tom Lane To: Atisss hh <125atishprak@gmail.com> cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut Subject: Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade In-reply-to: References: <19848-c4d5bc38ad4ab8b8@postgresql.org> Comments: In-reply-to Atisss hh <125atishprak@gmail.com> message dated "Sat, 07 Nov 2026 20:46:33 +0530" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3773196.1794453189.0@sss.pgh.pa.us> Date: Wed, 11 Nov 2026 22:13:20 -0500 Message-ID: <3773206.1794453200@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3773196.1794453189.1@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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0002-fix-sql-function-parameter-names.patch"; charset="us-ascii" Content-ID: <3773196.1794453189.2@sss.pgh.pa.us> Content-Description: 0002-fix-sql-function-parameter-names.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/fu= nctioncmds.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 =3D true; if (parameterTypes_list) *parameterTypes_list =3D lappend_oid(*parameterTypes_list, toid); + if (inParameterNames_list) + *inParameterNames_list =3D lappend(*inParameterNames_list, + makeString(fp->name ? fp->name : pstrdup(""))); } = /* handle output parameters */ @@ -418,9 +421,6 @@ interpret_function_parameter_list(ParseState *pstate, have_names =3D true; } = - if (inParameterNames_list) - *inParameterNames_list =3D 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 *lang= uageName, = pinfo->fname =3D funcname; pinfo->nargs =3D list_length(parameterTypes); + Assert(pinfo->nargs =3D=3D list_length(inParameterNames)); pinfo->argtypes =3D palloc_array(Oid, pinfo->nargs); pinfo->argnames =3D palloc_array(char *, pinfo->nargs); - for (int i =3D 0; i < list_length(parameterTypes); i++) + for (int i =3D 0; i < pinfo->nargs; i++) { char *s =3D strVal(list_nth(inParameterNames, i)); = diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/rul= eutils.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 =3D {0}; Datum tmp; Node *n; = dpns.funcname =3D pstrdup(NameStr(((Form_pg_proc) GETSTRUCT(proctup))->p= roname)); - numargs =3D get_func_arg_info(proctup, - &argtypes, &argnames, &argmodes); - dpns.numargs =3D numargs; - dpns.argnames =3D argnames; + + proargnames =3D SysCacheGetAttr(PROCOID, proctup, + Anum_pg_proc_proargnames, + &isNull); + if (isNull) + proargnames =3D PointerGetDatum(NULL); /* just to be sure */ + + proargmodes =3D SysCacheGetAttr(PROCOID, proctup, + Anum_pg_proc_proargmodes, + &isNull); + if (isNull) + proargmodes =3D PointerGetDatum(NULL); /* just to be sure */ + + dpns.numargs =3D get_func_input_arg_names(proargnames, proargmodes, + &dpns.argnames); = tmp =3D SysCacheGetAttrNotNull(PROCOID, proctup, Anum_pg_proc_prosqlbody= ); n =3D stringToNode(TextDatumGetCString(tmp)); diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/fun= capi.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_proce= dure.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] =3D names ? pstrdup(names[i]) : NULL; + proc->argnames[pos] =3D + (names && names[i][0] !=3D '\0') ? pstrdup(names[i]) : NULL; = pos++; } diff --git a/src/test/modules/plsample/plsample.c b/src/test/modules/plsam= ple/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 =3D SearchSysCache1(PROCOID, @@ -149,23 +150,34 @@ plsample_func_handler(PG_FUNCTION_ARGS) * Iterate through all of the function arguments, printing each input * value. */ + inargno =3D 0; for (int i =3D 0; i < numargs; i++) { - Oid argtype =3D pl_struct->proargtypes.values[i]; + Oid argtype =3D argtypes[i]; char *value; = + if (argmodes && + (argmodes[i] =3D=3D PROARGMODE_OUT || + argmodes[i] =3D=3D PROARGMODE_TABLE)) + continue; /* skip OUT arguments */ + type_tuple =3D SearchSysCache1(TYPEOID, ObjectIdGetDatum(argtype)); if (!HeapTupleIsValid(type_tuple)) elog(ERROR, "cache lookup failed for type %u", argtype); = type_struct =3D (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 =3D OutputFunctionCall(&arg_out_func[i], fcinfo->args[i].value); + value =3D 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 p= oint, 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 prec= ision) +drop cascades to function functest_out_point2(double precision,double pre= cision) +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/regre= ss/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); ------- =_aaaaaaaaaa0--