Thread: 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
Attachment
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
Attachment
> 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