Re: BUG #18319: Logical Replication updates causing duplication of row if evaluation filter is set to the same field

On Thu, Feb 1, 2024 at 6:09 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      18319
> Logged by:          Daniele Ignesti
> Email address:      ignestid@gmail.com
> PostgreSQL version: 15.5
> Operating system:   OEL 8.8
> Description:
>
> Good Morning,
>
> i'm facing the following issue:
> i have a publication (pub1) with publish of insert and update transactions
> made on table tab1 .
> if i update tab1 with the following statement:
>
> update tab1 set field1 = 'value2' where field1='value1';
>
> logical decoding on subscriber process read the transaction , and following
> the guidelines of  "31.3.3. UPDATE Transformations"  (
>
https://www.postgresql.org/docs/current/logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-TRANSFORMATIONS
> ) , the subscriber try as first thing the delete of old row and then the
> insert of the new row .
>
> What i think is a bug ,  is that the delete on old row is never done unless
> you specify publishing deletes on publications.
> So not all updates are published on subscriber unless you explicitily set
> publish deletes on publisher.
>
> Regards,
>
> Daniele
>

Hi, can you please provide details about your CREATE TABLE and the
CREATE PUBLICATION commands? e.g. you don't say what the row filter
and replica identity looked like.

I tried guessing your scenario, but on dev HEAD it seemed to work as expected.

(below is what I tried)

Publisher:

test_pub=# create table t1(a int, primary key (a));
test_pub=# create publication pub1 for table t1 where (a < 10) with
(publish = 'insert,update');
test_pub=# insert into t1 values(1);
test_pub=# insert into t1 values(2);

~

Subscriber:

test_sub=# create subscription sub1 connection 'dbname=test_pub'
publication pub1;
test_sub=# select * from t1;
 a
---
 1
 2
(2 rows)

~

Try some updates:

test_pub=# update t1 set a=3 where a=2;

test_sub=# select * from t1;
 a
---
 1
 3
(2 rows)

test_pub=# update t1 set a=99 where a=1;

test_sub=# select * from t1;
 a
---
 3
(1 row)

======
Kind Regards,
Peter Smith.
Fujitsu Australia