Re: [HACKERS] Logical replication and inheritance - Mailing list pgsql-hackers

On 2017/04/13 0:10, Robert Haas wrote:
> On Wed, Apr 5, 2017 at 8:25 AM, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> After thinking about it some more, I think the behavior we want would be
>> that changes to inheritance would reflect in the publication membership.
>>  So if you have a partitioned table, adding more partitions over time
>> would automatically add them to the publication.
> 
> I think the threshold question here is: is the partitioned table a
> member of the publication, or are the partitions members of the
> publication?

One more question could be - are we going to handle this specially only
for the partitioned tables?  Of course, if we use an approach with general
inheritance in mind, it will cover the partitioned table case, but we
might have to reconsider if the generality makes a solution prohibitive.

> Probably both should be allowed.  If you add a partition
> to the publication by name, then we should publish exactly that
> partition, full stop.

Later if someone adds the parent, current code would try to add the
partition again.

create table bar (a int);
create publication mypub for table bar;

create table foo (a int);
create table baz () inherits (foo);

alter table bar inherit foo;
alter publication mypub add table foo;
ERROR:  relation "bar" is already member of publication "mypub"

One way to avoid the error, is to add ONLY foo:

alter publication mypub add table only foo;
ALTER PUBLICATION

But then other children (baz) is not added.

There should be some way to avoid the error if the parent is added to the
same publication as the one which a child is already part of.  I think it
should be a no-op.  Sent a patch for that in a separate thread.

> If you add the partitioned table, then there is
> a choice of behaviors, including:
> 
> (1) That's an error; the user should publish the partitions instead.
> (2) That's a shorthand for all partitions, resolved at the time the
> table is added to the publication.  Later changes affect nothing.
> (3) All partitions are published, and changes in the set of partitions
> change what is published.
> 
> In my view, (3) is more desirable than (1) which is more desirable than (2).

Agree with that order.

>> We could implement this either by updating pg_publication_rel as
>> inheritance changes, or perhaps more easily by checking and expanding
>> inheritance in the output plugin/walsender.  There might be subtle
>> behavioral differences between those two approaches that we should think
>> through.  But I think one of these two should be done.
> 
> The latter approach sounds better to me.

Which, IIUC, means OpenTableList() called by both CreatePublication() and
AlterPublicationTables() does not expand inheritance and hence
pg_publication_rel entries are created only for the specified relation.
That is an important consideration because of pg_dump.  See below:

create table foo (a int);
create table bar () inherits (foo);
create publication mypub for table foo;  -- bar is added too.

$ pg_dump -s | psql -e test
<snip>
ALTER PUBLICATION mypub ADD TABLE foo;
ERROR:  relation "bar" is already member of publication "mypub"

This however ain't a problem if we consider my above suggestion to make
duplicate addition to publication a no-op.

Thanks,
Amit