Thread: BUG #17993: FK issue on logical replication table

BUG #17993: FK issue on logical replication table

From
PG Bug reporting form
Date:
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


Re: BUG #17993: FK issue on logical replication table

From
Amit Kapila
Date:
On Sun, Jun 25, 2023 at 12:57 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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.
>

Isn't the behavior you are seeing is same as what is documented in the
description of GUC "session_replication_role"? See [1]. You might want
to set the value of "session_replication_role" as origin/local to see
if that works for your use case.

[1] - https://www.postgresql.org/docs/devel/runtime-config-client.html

--
With Regards,
Amit Kapila.