Re: Logical Replication Custom Column Expression

Just one correction for the subscriber
On Subscriber:

test_sub=# CREATE TABLE tab(id int *pkey*, description varchar,
subscription varchar *pkey*);
CREATE TABLE

The subscription table should have the same primary key columns as the
publisher plus one more.
We need to make sure that on update only the same origin data is
being updated.

Στις Τετ 23 Νοε 2022 στις 1:24 π.μ., ο/η Peter Smith <smithpb2250(at)gmail(dot)com>
έγραψε:

> On Wed, Nov 23, 2022 at 7:38 AM Stavros Koureas
> <koureasstavros(at)gmail(dot)com> wrote:
> >
> > Reading more carefully what you described, I think you are interested in
> getting something you call origin from publishers, probably some metadata
> from the publications.
> >
> > This identifier in those metadata maybe does not have business value on
> the reporting side. The idea is to use a value which has specific meaning
> to the user at the end.
> >
> > For example assigning 1 for tenant 1, 2 for tenant 2 and so one, at the
> end based on a dimension table which holds this mapping the user would be
> able to filter the data. So programmatically the user can set the id value
> of the column plus creating the mapping table from an application let’s say
> and be able to distinguish the data.
> >
> > In addition this column should have the ability to be part of the
> primary key on the subscription table in order to not conflict with lines
> from other tenants having the same keys.
> >
> >
>
> I was wondering if a simpler syntax solution might also work here.
>
> Imagine another SUBSCRIPTION parameter that indicates to write the
> *name* of the subscription to some pre-defined table column:
> e.g. CREATE SUBSCRIPTION subname FOR PUBLICATION pub_tenant_1
> CONNECTION '...' WITH (subscription_column);
>
> Logical Replication already allows the subscriber table to have extra
> columns, so you just need to manually create the extra 'subscription'
> column up-front.
>
> Then...
>
> ~~
>
> On Publisher:
>
> test_pub=# CREATE TABLE tab(id int primary key, description varchar);
> CREATE TABLE
>
> test_pub=# INSERT INTO tab VALUES (1,'one'),(2,'two'),(3,'three');
> INSERT 0 3
>
> test_pub=# CREATE PUBLICATION tenant1 FOR ALL TABLES;
> CREATE PUBLICATION
>
> ~~
>
> On Subscriber:
>
> test_sub=# CREATE TABLE tab(id int, description varchar, subscription
> varchar);
> CREATE TABLE
>
> test_sub=# CREATE SUBSCRIPTION sub_tenant1 CONNECTION 'host=localhost
> dbname=test_pub' PUBLICATION tenant1 WITH (subscription_column);
> CREATE SUBSCRIPTION
>
> test_sub=# SELECT * FROM tab;
> id | description | subscription
> ----+-------------+--------------
> 1 | one | sub_tenant1
> 2 | two | sub_tenant1
> 3 | three | sub_tenant1
> (3 rows)
>
> ~~
>
> Subscriptions to different tenants would be named differently.
>
> And using other SQL you can map/filter those names however your
> application wants.
>
> ------
> Kind Regards,
> Peter Smith.
> Fujitsu Australia
>