pg_get_publication_tables() output duplicate relid - Mailing list pgsql-hackers

From houzj.fnst@fujitsu.com
Subject pg_get_publication_tables() output duplicate relid
Date
Msg-id OS0PR01MB5716E97F00732B52DC2BBC2594989@OS0PR01MB5716.jpnprd01.prod.outlook.com
Whole thread Raw
Responses Re: pg_get_publication_tables() output duplicate relid
List pgsql-hackers
Hi hackers,

In another thread[1], we found the pg_get_publication_tables function will output
duplicate partition relid when adding both child and parent table to the
publication(pubviaroot = false).

Example:
create table tbl1 (a int) partition by range (a);
create table tbl1_part1 partition of tbl1 for values from (1) to (10);
create table tbl1_part2 partition of tbl1 for values from (10) to (20);
create publication pub for table
tbl1, tbl1_part1 with (publish_via_partition_root=false);

select * from pg_get_publication_tables('pub');
 relid
-------
 16387
 16390
 16387

The reason of the duplicate output is that:

pg_get_publication_tables invokes function GetPublicationRelations internally.
In GetPublicationRelations(), it will add the oid of partition 'tbl1_part1'
twice. First time from extracting partitions from the specified parent table
'tbl1', second time from the explicitly specified partition 'tbl1_part1'.


I am not sure is this behavior expected as it seems natural for
pg_get_publication_tables to return duplicate-free relid list. OTOH, there
seems no harm for the current behavior(duplicate output), it doesn't affect the
initial sync and change replication when using logical replication.

Personally, I think it might be better to make the output of
pg_get_publication_tables duplicate-free, because the change happened on each
output relid will only be replicated once. So, it seems more consistent to
output each relid only once.

Thoughts ?

(Attach a patch which make the output duplicate-free)

[1] https://www.postgresql.org/message-id/CAJcOf-eURu03QNmD%3D37PtsxuNW4nBGN3G_FdRMBx_tpkeyzDUw%40mail.gmail.com

Best regards,
Hou zj

Attachment

pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Logical Replication - improve error message while adding tables to the publication in check_publication_add_relation
Next
From: Masahiko Sawada
Date:
Subject: Re: parallel vacuum comments