From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | a(dot)kutepow(at)prodat-sql(dot)de |
Subject: | BUG #18019: misbehaviour by replication |
Date: | 2023-07-12 10:08:54 |
Message-ID: | 18019-21e3fdb5d9057921@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18019
Logged by: André Kutepow
Email address: a(dot)kutepow(at)prodat-sql(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2023-07-12 12:49:50 | Re: BUG #18018: Homebrew link is broken |
Previous Message | suyu.cmj | 2023-07-12 07:20:57 | Re: The same 2PC data maybe recovered twice |