pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18319: Logical Replication updates causing duplication of row if evaluation filter is set to the same field
2+ messages / 2 participants
[nested] [flat]
* BUG #18319: Logical Replication updates causing duplication of row if evaluation filter is set to the same field
@ 2024-01-31 15:42 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2024-01-31 15:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: ignestid@gmail.com
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-...
) , 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18319: Logical Replication updates causing duplication of row if evaluation filter is set to the same field
@ 2024-02-02 00:35 Peter Smith <smithpb2250@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Smith @ 2024-02-02 00:35 UTC (permalink / raw)
To: ignestid@gmail.com; pgsql-bugs@lists.postgresql.org
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-...
> ) , 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-02-02 00:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-31 15:42 BUG #18319: Logical Replication updates causing duplication of row if evaluation filter is set to the same field PG Bug reporting form <noreply@postgresql.org>
2024-02-02 00:35 ` Peter Smith <smithpb2250@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox