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 1xPjB7-00000000rgR-0E9I for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:22:06 +0000 Received: from localhost ([127.0.0.1]:57128 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPirG-000000049ly-0JS2 for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:37462) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi9G-00000003iyK-24yL for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:16:06 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi9D-00000000j4O-2BOy for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:16:05 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=zFqtfKAk4KWK6jNZsWTcIdduECGOpvMBso9/APvtbn0=; b=NtMPjsGJG8JkjGYFQTrxof9NW8 nBY1XfmdiTVYlE7G/V4Mi0toswEU7/HdrZmgYLBVIw6P3oekvwLnanlp1K9dG5kycYr9uNzQkmEYI h4MXF3cC3BtPaFOo7rE63utgORj0tVSdmsNQ7KUrB6HDGuKXOsEyF1x+rtDVIF95Ql+6xXfqojU9O 3lF1gyGwsWLFO+yExrrNfbbjJVY9m/Uzuqah9UZQ05XZ17K4cG+ww+Cm8CAXX7HCPZ9C8rOYCVfJm VqUEExordOHaVCvdSCuLPBF5cxeADGSs3R8lfg8wbOoH0Bb6XMgXtI5KbQW96Wv5xwP56G83eukyy m6fFgNMA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPi9E-000000021j9-2Izx for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:16:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPi9C-000000056gs-3vAL for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 15:16:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 125atishprak@gmail.com Reply-To: 125atishprak@gmail.com, pgsql-bugs@lists.postgresql.org Date: Sat, 07 Nov 2026 15:15:42 +0000 Message-ID: <19848-c4d5bc38ad4ab8b8@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 =3D $1; END; SELECT pg_get_functiondef('fa'::regproc); -- ... WHERE (s.a =3D 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.