Sure I understand and neither do I have good knowledge of what else could be influenced by such a change.
If the value of the column is the subscriber name has no benefit to this idea of merging multiple upstreams with same primary keys, later you describe the "connection dbname", yes this could be a possibility.
I do not fully understand that part "how will the initial tablesync COPY efficiently assign these subscriber name column values?"
Why is difficult that during the initial sync put everywhere the same value for all rows of the same origin?
On Fri, Nov 25, 2022 at 9:43 PM Stavros Koureas
<koureasstavros@gmail.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.