Re: Logical replication and inheritance

On 2017/04/14 10:57, Petr Jelinek wrote:
> I don't think inheritance and partitioning should behave the same in
> terms of logical replication.

I see.

>
> For me the current behavior with inherited tables is correct.

OK.

By the way, what do you think about the pg_dump example/issue I mentioned?
Is that a pg_dump problem or backend? To reiterate, if you add an
inheritance parent to a publication, dump the database, and restore it
into another, an error occurs. Why? Because every child table is added
*twice* because of the way publication tables are dumped. Once by itself
and again via inheritance expansion when the parent is added. Adding a
table again to the same publication is currently an error, which I was
wondering if it could be made a no-op instead.

> What I would like partitioned tables support to look like is that if we
> add partitioned table, the data decoded from any of the partitions would
> be sent as if the change was for that partitioned table so that the
> partitioning scheme on subscriber does not need to be same as on
> publisher. That's nontrivial to do though probably.

I agree that it'd be nontrivial. I'm not sure if you're also implying
that a row decoded from a partition is *never* published as having been
inserted into the partition itself. A row can end up in a partition via
both the inserts into the partitioned table and the partition itself.
Also, AFAICT, obviously the output pluggin would have to have a dedicated
logic to determine which table to publish a given row as coming from
(possibly the root partitioned table), since nothing decode-able from WAL
is going to have that information.

Also, with the current state of partitioning, if a row decoded and
published as coming from the partitioned table had no corresponding
partition defined on the destination server, an error will occur in the
subscription worker I'd guess. Or may be we don't assume anything about
whether the table on the subscription end is partitioned or not.

Anyway, that perhaps also means that for time being, we might need to go
with the following option that Robert mentioned (I suppose strictly in the
context of partitioned tables, not general inheritance):

(1) That's an error; the user should publish the partitions instead.

That is, we should make adding a partitioned table to a publication a user
error (feature not supported).

Thanks,
Amit