Обсуждение: BUG #18019: misbehaviour by replication

The following bug has been logged on the website:

Bug reference:      18019
Logged by:          André Kutepow
Email address:      a.kutepow@prodat-sql.de
PostgreSQL version: 15.3
Operating system:   Windows Server 2016
Description:

-------------------------------------------------------------------------------
[publisher]

ALTER TABLE art ADD COLUMN IF NOT EXISTS ak_replicate bool NOT null DEFAULT
false;

DROP INDEX IF EXISTS pub_idx__art;
CREATE UNIQUE INDEX pub_idx__art on art(ak_nr, ak_replicate); --ak_nr is
PK
ALTER TABLE art REPLICA IDENTITY USING INDEX pub_idx__art;

DROP PUBLICATION IF EXISTS pub_x2e__art;
CREATE PUBLICATION pub_x2e__art FOR TABLE art WHERE (ak_replicate IS
true);

SELECT pg_drop_replication_slot('slot_x2e__art');
SELECT pg_create_logical_replication_slot('slot_x2e__art', 'pgoutput');
-------------------------------------------------------------------------------
[subscribler]

ALTER TABLE art ADD COLUMN IF NOT EXISTS ak_replicate bool NOT null DEFAULT
false;

--Drop Subscription
ALTER SUBSCRIPTION sub_x2e__art DISABLE;
ALTER SUBSCRIPTION sub_x2e__art SET (slot_name = NONE);
DROP SUBSCRIPTION sub_x2e__art;

CREATE SUBSCRIPTION sub_x2e__art CONNECTION 'host=127.0.0.1 port=5435
dbname=REP-X2E user=repuser password=pass' PUBLICATION pub_x2e__art WITH
(create_slot = false, enabled = false, slot_name = 'slot_x2e__art'); 
ALTER SUBSCRIPTION sub_x2e__art ENABLE;
ALTER SUBSCRIPTION sub_x2e__art REFRESH PUBLICATION; 
-------------------------------------------------------------------------------
[publisher]

INSERT INTO art(ak_nr, ak_replicate)
         VALUES('TEST_ARTIKEL_001', true);
         
SELECT ak_nr, ak_replicate FROM art WHERE ak_nr ilike 'test_artikel%'
-->> "TEST_ARTIKEL_001"; true;
-------------------------------------------------------------------------------
[subscribler]
SELECT ak_nr, ak_replicate FROM art WHERE ak_nr ilike 'test_artikel%'
-->> "TEST_ARTIKEL_001"; true;

INSERT INTO auftg(ag_nr, ag_aknr) --ag_aknr VARCHAR(40) NOT NULL REFERENCES
art ON UPDATE CASCADE,
          VALUES ('AG_001', 'TEST_ARTIKEL_001');

SELECT ag_nr, ag_aknr FROM auftg WHERE ag_aknr ilike 'test_artikel%'
-->> "AG_001"; "TEST_ARTIKEL_001"

DELETE FROM art WHERE ak_nr ilike 'test_artikel%'
--ERROR:  Auf Schlüssel (ak_nr)=(TEST_ARTIKEL_001) wird noch aus Tabelle
»auftg« verwiesen.Aktualisieren oder Löschen in Tabelle »art« verletzt
Fremdschlüssel-Constraint »auftg_ag_aknr_fkey« von Tabelle »auftg« 
--FEHLER:  Aktualisieren oder Löschen in Tabelle »art« verletzt
Fremdschlüssel-Constraint »auftg_ag_aknr_fkey« von Tabelle »auftg«
--SQL-Status: 23503
--Detail: Auf Schlüssel (ak_nr)=(TEST_ARTIKEL_001) wird noch aus Tabelle
»auftg« verwiesen.

-->this is the right behavior
-------------------------------------------------------------------------------
[publisher]
UPDATE art SET ak_replicate = false WHERE ak_nr = 'TEST_ARTIKEL_001'
-------------------------------------------------------------------------------
[subscribler]
SELECT ak_nr, ak_replicate FROM art WHERE ak_nr ilike 'test_artikel%'
-->> leer

SELECT ag_nr, ag_aknr FROM auftg WHERE ag_aknr ilike 'test_artikel%'
-->> "AG23-00201"; "TEST_ARTIKEL_001"
-------------------------------------------------------------------------------
this is (imho) misbehaviour! 
replication should not break integrity and break references logic!
or explain why it's right and how to live with it?

At Wed, 12 Jul 2023 10:08:54 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in 
(In short, foreign constraint trigger doesn't fire on apply woker.)
> this is (imho) misbehaviour! 
> replication should not break integrity and break references logic!
> or explain why it's right and how to live with it?

You can activate the fkey constraints on the subscriber by setting the
underlying triggers to ENABLE ALWAYS (or REPLICA) using the ALTER
TABLE command.

By design, triggers are not active on subscribers by default. It might
seem peculiar, especially in the context of foreign key constraints,
as check constraints are operational on subscribers. Moreover,
although the documentation mentions that the purpose of the behavior
is avoid propagating data between tables again on subscribers, foreign
key triggers don't contribute to this.

If we're willing to enable constraint triggers on subscribers by
default, CreateTrigger can choose trigger_fires_when passed to
CreateTriggerFiringOn based on constraintOid.  However, I'm unceratin
about the case of CREATE CONSTRAINT TRIGGER.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Dear André,

Thanks for the bug report!

We have reproduced the scenario, and slightly simplified the steps and changed
the table/column names to be more generic. Please see the attached script.

You have a PUBLICATION using a row filter “WHERE (should_replicate IS true)”

When you updated that filter column from TRUE to FALSE, we think you hoped that it would not replicate.
But actually this behaviour is correct according to the documentation describing UPDATE Transformations [1]

Specifically “If the old row satisfies the row filter expression (it was sent to the subscriber) but the new row
doesn't,then, from a data consistency perspective the old row should be removed from the subscriber. So the UPDATE is
transformedinto a DELETE.”
 

[1]
https://www.postgresql.org/docs/current/logical-replication-row-filter.html#LOGICAL-REPLICATION-ROW-FILTER-TRANSFORMATIONS

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

> When you updated that filter column from TRUE to FALSE, we think you hoped that it would not replicate.
no, I hoped thar it would replicate, but I was hoping to get an error in 
the log that this was not possible, since this record is referenced from 
another table and cannot be simply deleted, what we see by a regular 
DELETE attempt

Best Regards,
Andre

On Thu, Jul 13, 2023 at 2:33 PM André Kutepow <a.kutepow@prodat-sql.de> wrote:
>
> > When you updated that filter column from TRUE to FALSE, we think you hoped that it would not replicate.
> no, I hoped thar it would replicate, but I was hoping to get an error in
> the log that this was not possible, since this record is referenced from
> another table and cannot be simply deleted, what we see by a regular
> DELETE attempt
>

By default, triggers are not enabled while applying the changes on the
subscriber side. You can use Alter Table .. Enable Trigger command to
enable it as mentioned in docs [1] as well (The apply process on the
subscriber ...).

[1] - https://www.postgresql.org/docs/devel/logical-replication-architecture.html

--
With Regards,
Amit Kapila.

On Thu, Jul 13, 2023 at 1:19 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> At Wed, 12 Jul 2023 10:08:54 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in
> (In short, foreign constraint trigger doesn't fire on apply woker.)
> > this is (imho) misbehaviour!
> > replication should not break integrity and break references logic!
> > or explain why it's right and how to live with it?
>
> You can activate the fkey constraints on the subscriber by setting the
> underlying triggers to ENABLE ALWAYS (or REPLICA) using the ALTER
> TABLE command.
>
> By design, triggers are not active on subscribers by default. It might
> seem peculiar, especially in the context of foreign key constraints,
> as check constraints are operational on subscribers. Moreover,
> although the documentation mentions that the purpose of the behavior
> is avoid propagating data between tables again on subscribers, foreign
> key triggers don't contribute to this.
>
> If we're willing to enable constraint triggers on subscribers by
> default, CreateTrigger can choose trigger_fires_when passed to
> CreateTriggerFiringOn based on constraintOid.  However, I'm unceratin
> about the case of CREATE CONSTRAINT TRIGGER.
>

It might be a good idea to discuss this topic on -hackers as this is
the way it works from the beginning. So changing it requires broader
discussion.

--
With Regards,
Amit Kapila.

At Thu, 13 Jul 2023 16:00:40 +0530, Amit Kapila <amit.kapila16@gmail.com> wrote in 
> It might be a good idea to discuss this topic on -hackers as this is
> the way it works from the beginning. So changing it requires broader
> discussion.

Thanks for the suggestion! I posted this in -hackers.

https://www.postgresql.org/message-id/20230719.154957.1248869963398587687.horikyota.ntt%40gmail.com

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center