From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: salmanbasmechi@gmail.com
Subject: BUG #17993: FK issue on logical replication table
Date: Sat, 24 Jun 2023 06:01:25 +0000
Message-ID: <17993-2c0731369915b8ec@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, salmanbasmechi@gmail.com
Subject: Re: BUG #17993: FK issue on logical replication table
In-Reply-To: <17993-2c0731369915b8ec@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox