Re: Logical Replication Custom Column Expression

On Fri, Nov 25, 2022 at 9:43 PM Stavros Koureas
<koureasstavros(at)gmail(dot)com> wrote:
>
> Yes, if the property is on the subscription side then it should be applied for all the tables that the connected publication is exposing.
> So if the property is enabled you should be sure that this origin column exists to all of the tables that the publication is exposing...
>
> Sure this is the complete idea, that the subscriber should match the PK of origin, <previous_pkey>
> As the subscription table will contain same key values from different origins, for example:
>
> For publisher1 database table
> id pk integer | value character varying
> 1 | testA1
> 2 | testA2
>
> For publisher2 database table
> id pk integer | value character varying
> 1 | testB1
> 2 | testB2
>
> For subscriber database table
> origin pk character varying | id pk integer | value character varying
> publisher1 | 1 | testA1
> publisher1 | 2 | testA2
> publisher2 | 1 | testB1
> publisher2 | 2 | testB2
>
> All statements INSERT, UPDATE, DELETE should always include the predicate of the origin.
>

This sounds similar to what I had posted [1] although I was saying the
generated column value might be the *subscriber* name, not the origin
publisher name. (where are you getting that value from -- somehow from
the subscriptions' CONNECTION dbname?)

Anyway, regardless of the details, please note -- my idea was really
intended just as a discussion starting point to demonstrate that
required functionality might be achieved using a simpler syntax than
what had been previously suggested. But in practice there may be some
problems with this approach -- e.g. how will the initial tablesync
COPY efficiently assign these subscriber name column values?

------
[1] https://www.postgresql.org/message-id/CAHut%2BPuZowXd7Aa7t0nqjP6afHMwJarngzeMq%2BQP0vE2KKLOgQ%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia.