Обсуждение: pg_publication_tables: return NULL attnames when no column list is specified

On 2026-Mar-25, Roberto Mello wrote:

> The problem is that pg_get_publication_tables() in pg_publication.c
> (the set-returning function backing the pg_publication_tables view)
> erases this distinction.  When prattrs is NULL, it opens the table,
> iterates all eligible attributes, and builds a synthetic int2vector of
> all current columns. The view then shows the same attnames output for
> both cases.

I agree that this is wrong.  This distinction was explicitly discussed
when the column-list feature was developed.  I don't think we can
backpatch the fix though, out of fear that we would break something for
existing users; but we should definitely fix it for pg19.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"But static content is just dynamic content that isn't moving!"
                http://smylers.hates-software.com/2007/08/15/fe244d0c.html

On Thu, Mar 26, 2026 at 11:49 AM Roberto Mello <roberto.mello@gmail.com> wrote:
>
> On Wed, Mar 25, 2026 at 5:32 PM Peter Smith <smithpb2250@gmail.com> 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 headers
>
>
> 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=# CREATE TABLE my_table (id int PRIMARY KEY, name text, status text);
CREATE TABLE
postgres=# ALTER TABLE my_table ADD COLUMN old_col text;
ALTER TABLE
postgres=# ALTER TABLE my_table DROP COLUMN old_col;
ALTER TABLE
postgres=# 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

=========================
On head - without patch:
=========================

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

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

postgres=# 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) = c.relnatts
         THEN NULL
         ELSE gpt.attnames
    END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c      ON c.relname = gpt.tablename
JOIN pg_publication p ON p.pubname = gpt.pubname
LEFT JOIN pg_publication_rel pr
       ON pr.prrelid = c.oid
      AND pr.prpubid = p.oid
WHERE gpt.tablename = '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)

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

============
After patch:
============

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

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

postgres=# 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) = c.relnatts
         THEN NULL
         ELSE gpt.attnames
    END AS heuristic_result
FROM pg_publication_tables gpt
JOIN pg_class c      ON c.relname = gpt.tablename
JOIN pg_publication p ON p.pubname = gpt.pubname
LEFT JOIN pg_publication_rel pr
       ON pr.prrelid = c.oid
      AND pr.prpubid = p.oid
WHERE gpt.tablename = '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