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 по дате отправления: