pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 125atishprak@gmail.com
Subject: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
Date: Sat, 07 Nov 2026 15:15:42 +0000
Message-ID: <19848-c4d5bc38ad4ab8b8@postgresql.org> (raw)

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.







view thread (5+ messages)  latest in thread

Message-ID: <19848-c4d5bc38ad4ab8b8@postgresql.org>
Permalink:  ../19848-c4d5bc38ad4ab8b8@postgresql.org/
Also on:    postgresql.org/message-id/19848-c4d5bc38ad4ab8b8@postgresql.org

 · 

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: noreply@postgresql.org, 125atishprak@gmail.com
  Subject: Re: BUG #19848: BEGIN ATOMIC with unnamed/OUT params breaks restore and pg_upgrade
  In-Reply-To: <19848-c4d5bc38ad4ab8b8@postgresql.org>
  References: <19848-c4d5bc38ad4ab8b8@postgresql.org>

* 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