The following bug has been logged on the website:
Bug reference: 17993
Logged by: Salman Basmechi
Email address: salmanbasmechi@gmail.com
PostgreSQL version: 15.3
Operating system: Ubuntu 22.04
Description:
Dear Postgres developer,
I am Salman Basmechi from Iran. I found a bug in logical replication
behavior. it seems foreign key constraint does not apply in logical
replication mechanism.
Below scripts is a sample you can run in a single machine and see the wrong
behavior.
publication side:
create database pub_basmechi;
create table material (id bigserial primary key, name text);
create publication pub for table material;
create pg_create_logical_replication_slot("slot_1", "pgoutput");
insert into material (name) values ("mobile"); // we expected material with
id = 1 create after run this line
subscription side:
create database sub_basmechi;
create table material (id bigserial primary key, name text);
create table product (id bigserial primary key, material_id bigint
references material, name text);
create subscription sub
connection 'host=localhost port=5432 dbname=pub_basmechi user=postgres
password=admin'
publication pub
with (slot_name=slot_1, create_slot=false);
after replicate material 'mobile' into sub_basmechi database:
// we expected material 'mobile' with id = 1 exist and we can reference
that
insert into product (material_id, name) values (1, 'product_mobile')
// we expected have one product in table product with name =
'product_mobile' that reference to material with id = 1 and name =
'mobile'
now delete material in pub_basmechi database:
delete from material where id = 1;
run select query in sub_basmechi database:
select * from material;
// unexpected result. material with id = 1 has been deleted from
sub_basmechi and product 'product_mobile' exists and reference to material
with id = 1. but material does not exist!
select * from product;
// we see one record in product that inserted before that reference to null
point in material table
Finally, it seems FK constraint does not apply in replication and delete
signal from publisher can remove any referenced data in subscriber.
In your docs there is not any line that notice this behavior, and if it is
normal, please update your docs and notify me by email.
Thank you,
Salman Basmechi,
Senior Software Engineer @systemgroup.net
salmanbasmechi@gmail.com
+989129335607
