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
0001-fix-duplicate-table-in-pg_publication_tables.patch
Description: 0001-fix-duplicate-table-in-pg_publication_tables.patch
