MIME-Version: 1.0
References: 
 <CAKz==b+k7OwOib__LgfDadUzW9jFrbQ9FqdERpsufi8d7F=vkQ@mail.gmail.com>
 <CAHut+PuhDwEySMSMz_4hgmFNG75Wx=Wv4guuaUSGi6e3ixsUUw@mail.gmail.com>
 <CAKz==bLEjr+706MkmpHsd1wfz0EqYzEXJVb2cn2mxp2nnHf=kQ@mail.gmail.com>
In-Reply-To: 
 <CAKz==bLEjr+706MkmpHsd1wfz0EqYzEXJVb2cn2mxp2nnHf=kQ@mail.gmail.com>
From: Ajin Cherian <itsajin@gmail.com>
Date: Fri, 27 Mar 2026 15:06:54 +1100
Message-ID: 
 <CAFPTHDa-k7qV=H_UgojLs-denk8upJWG7s6xZwD4r4ZTP-=q5Q@mail.gmail.com>
Subject: Re: pg_publication_tables: return NULL attnames when no column list
 is specified
To: Roberto Mello <roberto.mello@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAFPTHDa-k7qV%3DH_UgojLs-denk8upJWG7s6xZwD4r4ZTP-%3Dq5Q%40mail.gmail.com>
Precedence: bulk

On Thu, Mar 26, 2026 at 11:49=E2=80=AFAM Roberto Mello <roberto.mello@gmail=
.com> wrote:
>
> On Wed, Mar 25, 2026 at 5:32=E2=80=AFPM Peter Smith <smithpb2250@gmail.co=
m> wrote:
>>
>> FYI - the patch failed to apply for me because of publication.out issues=
.
>>
>> There appear to be some missing trailing spaces in the result table head=
ers
>
>
> Apologies.
>
> Generated a v2 patch. Applied cleanly and tests passed.


Hello,

Tested the patch and the patch fixes the bug as described. My tests below:


postgres=3D# CREATE TABLE my_table (id int PRIMARY KEY, name text, status t=
ext);
CREATE TABLE
postgres=3D# ALTER TABLE my_table ADD COLUMN old_col text;
ALTER TABLE
postgres=3D# ALTER TABLE my_table DROP COLUMN old_col;
ALTER TABLE
postgres=3D# CREATE PUBLICATION pub_no_list FOR TABLE my_table;
CREATE PUBLICATION pub_explicit_all FOR TABLE my_table (id, name, status);
CREATE PUBLICATION
CREATE PUBLICATION

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
On head - without patch:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

postgres=3D# SELECT pubname, attnames
FROM pg_publication_tables
WHERE tablename =3D 'my_table'
ORDER BY pubname;
     pubname      |     attnames
------------------+------------------
 pub_explicit_all | {id,name,status}
 pub_no_list      | {id,name,status}
(2 rows)

postgres=3D# SELECT p.pubname, pr.prattrs
FROM pg_publication p
LEFT JOIN pg_publication_rel pr
       ON pr.prpubid =3D p.oid
JOIN pg_class c ON c.oid =3D pr.prrelid
WHERE c.relname =3D 'my_table'
ORDER BY p.pubname;
     pubname      | prattrs
------------------+---------
 pub_explicit_all | 1 2 3
 pub_no_list      |
(2 rows)

postgres=3D# SELECT
    p.pubname,
    pr.prattrs                          AS raw_catalog_attrs,
    gpt.attnames                        AS view_attnames,
    c.relnatts,
    array_length(gpt.attnames, 1)       AS synth_len,
    -- Replicating the buggy CASE WHEN heuristic from tablesync.c
    CASE WHEN array_length(gpt.attnames, 1) =3D c.relnatts
         THEN NULL
         ELSE gpt.attnames
    END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c      ON c.relname =3D gpt.tablename
JOIN pg_publication p ON p.pubname =3D gpt.pubname
LEFT JOIN pg_publication_rel pr
       ON pr.prrelid =3D c.oid
      AND pr.prpubid =3D p.oid
WHERE gpt.tablename =3D 'my_table'
ORDER BY p.pubname;
     pubname      | raw_catalog_attrs |  view_attnames   | relnatts |
synth_len | heuristic_result
------------------+-------------------+------------------+----------+------=
-----+------------------
 pub_explicit_all | 1 2 3             | {id,name,status} |        4 |
       3 | {id,name,status}
 pub_no_list      |                   | {id,name,status} |        4 |
       3 | {id,name,status}
(2 rows)

---------------------------------------------------------------------

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
After patch:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

postgres=3D# SELECT pubname, attnames
FROM pg_publication_tables
WHERE tablename =3D 'my_table'
ORDER BY pubname;
     pubname      |     attnames
------------------+------------------
 pub_explicit_all | {id,name,status}
 pub_no_list      |
(2 rows)

postgres=3D# SELECT p.pubname, pr.prattrs
FROM pg_publication p
LEFT JOIN pg_publication_rel pr ON pr.prpubid =3D p.oid
JOIN pg_class c ON c.oid =3D pr.prrelid
WHERE c.relname =3D 'my_table'
ORDER BY p.pubname;
     pubname      | prattrs
------------------+---------
 pub_explicit_all | 1 2 3
 pub_no_list      |
(2 rows)

postgres=3D# SELECT
    p.pubname,
    pr.prattrs                          AS raw_catalog_attrs,
    gpt.attnames                        AS view_attnames,
    c.relnatts,
    array_length(gpt.attnames, 1)       AS synth_len,
    -- Replicating the buggy CASE WHEN heuristic from tablesync.c
    CASE WHEN array_length(gpt.attnames, 1) =3D c.relnatts
         THEN NULL
         ELSE gpt.attnames
    END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c      ON c.relname =3D gpt.tablename
JOIN pg_publication p ON p.pubname =3D gpt.pubname
LEFT JOIN pg_publication_rel pr
       ON pr.prrelid =3D c.oid
      AND pr.prpubid =3D p.oid
WHERE gpt.tablename =3D 'my_table'
ORDER BY p.pubname;
     pubname      | raw_catalog_attrs |  view_attnames   | relnatts |
synth_len | heuristic_result
------------------+-------------------+------------------+----------+------=
-----+------------------
 pub_explicit_all | 1 2 3             | {id,name,status} |        4 |
       3 | {id,name,status}
 pub_no_list      |                   |                  |        4 |
         |
(2 rows)


One observation from reviewing the patch: the test suite covers the
partial column list and dropped column cases well, but is missing a
test for the two-publication conflict scenario (one pub with no list +
one pub with an explicit list of all columns on the same table). That
is the breaking change called out in the original report and probably
deserves its own regression test with a comment explaining the
expected behavior change for users in that configuration.
Patch LGTM otherwise.

regards,
Ajin Cherian
Fujitsu Australia