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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18319
> Logged by: Daniele Ignesti
> Email address: ignestid(at)gmail(dot)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