BUG #18019: misbehaviour by replication

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?

Responses

Browse pgsql-bugs by date

  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