BUG #18019: misbehaviour by replication
| От | PG Bug reporting form |
|---|---|
| Тема | BUG #18019: misbehaviour by replication |
| Дата | |
| Msg-id | 18019-21e3fdb5d9057921@postgresql.org обсуждение исходный текст |
| Ответы |
Re: BUG #18019: misbehaviour by replication
RE: BUG #18019: misbehaviour by replication |
| Список | pgsql-bugs |
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?
В списке pgsql-bugs по дате отправления: