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