Replicating DELETE commands together with row_filter does not work

[kouber]

I am unable to replicate DELETE commands when a row_filter is set.

When there is no filter and I simply add the table, everything works as expected:

SELECT pglogical.replication_set_add_table('default', 't1'::regclass, true);

Each DML command is replicated to the slave.

However when I specify a row_filter, the INSERTs and UPDATEs work as expected, but the DELETEs are not replicated for some reason.

SELECT pglogical.replication_set_add_table('default', 't1'::regclass, true, null, 'app_id = 5');

So issuing something like DELETE FROM t1 WHERE app_id=5 will delete data on the master, but not on the slave.

I have also tried to use another custom replication set:

SELECT pglogical.create_replication_set('mutu', replicate_insert := false, replicate_update := false, replicate_delete := true, replicate_truncate := true);

Then add t1 to both mutu and default on the master, and subscribe to both sets on the slave, but yet - the result is the same.

The interesting thing is that if I do not add the table to the default replication set and leave it to the custom one only, then the DELETEs are replicated.

When then I add the table again to the default set with a row_filter DELETEs again stop working (even when they used to work with the mutu set alone).

[kouber]

I found some workaround, yet I do not understand the difference.

Working solution (adding two separate subscribers):

SELECT pglogical.create_subscription(
  'pro1',
  'host=master-ip port=5432 dbname=am',
  '{mutu}'
);

SELECT pglogical.create_subscription(
  'pro2',
  'host=master-ip port=5432 dbname=am',
  '{default}'
);

Mixing 2 replication sets in one subscription does not work (i.e. DELETEs are not replicated).

SELECT pglogical.create_subscription(
  'pro1',
  'host=master-ip port=5432 dbname=am',
  '{default,mutu}'
);

[premum]

Hi

even I am facing similar issue when trying to replicate tables with row_filter enabled. But in my usecase, I have only one table added to one replication set and trying to replicate the same.

Still the delete operation doesn't work with postgres 9.6 Any ideas how to solve it.

[pkupriec]

Similar issue. postgresql 9.6 with pglogical 2.2. After defining replication set (not default) with row_filter and adding tables deletes does not replicate. Any ideas?

[pkupriec]

Found a solution that works for me. Seems the reason was that field used in my row_filter was not part of PK (that is REPLICA IDENTITY DEFAULT). So I've created a new unique index and use it as a replica identity.

Now I do not know why update worked properly...

[mpasquini]

Seems all is working as expected. Did you try to create 2 repset "del_tr" , "ins_upd" ?

SELECT pglogical.create_replication_set
('del_tr', 
replicate_insert := false,
replicate_update := false, 
replicate_delete := true, 
replicate_truncate := true);
  
SELECT pglogical.create_replication_set
('ins_upd', 
replicate_insert := true,
replicate_update := true, 
replicate_delete := false, 
replicate_truncate := false);  

And the add tables with row_filter only on 'ins_upd' :

 SELECT pglogical.replication_set_add_table
('ins_upd', 
't1'::regclass, 
true, null, 
'app_id = 5');
 SELECT pglogical.replication_set_add_table
('del_tr', 
't1'::regclass, 
true, null, null);

and on subscriber:

SELECT pglogical.create_subscription(
  'pro1',
  'host=master-ip port=5432 dbname=am',
  '{ins_upd,del_tr}'
);

For me this approach resolved for an INSERT issues where initial insert was filtered out and later update was skipped on subscriber due to missing pk. I guess it works also for you REPLICA IDENTITY DEFAULT should register the pk delete action in the wal file for logical decoding and replicate it because there is no filter on delete.

(Truncate can be set whatever you prefer)

[bpinto]

@mpasquini does this work for you with a single subscription? DELETE are not replicated in a single subscription on my testing. It only worked with two subscriptions:

SELECT pglogical.create_subscription(
  'pro1',
  'host=master-ip port=5432 dbname=am',
  '{ins_upd}'
);
SELECT pglogical.create_subscription(
  'pro1',
  'host=master-ip port=5432 dbname=am',
  '{del_tr}'
);

I haven't confirmed yet if the COPY statement produced will respect the row_filter condition since the del_tr replication set has no row_filter but also synchronizes the data.

---

Update: Running two subscriptions led me to a "duplicate key value violates unique constraint" which I believe makes sense since both subscriptions are trying to do the initial sync.

[bpinto]

@ringerc Do you know if this is supported?

[paulbandler]

@bpinto @kouber @mpasquini I have run into the same issue, running pglogical 2.3 with postgresql 12. Thanks to the information above I was able to get DELETES replicated using the 2 subscription approach, but concerned that this will double the number of connections needed, which for my use-case is a concern.

Has anyone been able to come up with a solution that only uses a single subscription?

[bpinto]

I have not and thus had to give up on this idea. :(