On Wed, Nov 23, 2022 at 7:38 AM Stavros Koureas <koureasstav...@gmail.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
