Обсуждение: Are Foreign Key Disabled During Logical Replication Initial Sync?

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

On 06.12.21 15:50, Avi Weinberg wrote:
> Does it mean that populating each table is done in a single 
> transaction?  If so, when I have tables with foreign keys between them, 
> is it guaranteed that logical replication will populates the tables in 
> the proper order so the foreign key will be enforced?  Or maybe the 
> foreign keys are disabled during initial sync and only after all tables 
> are populated the FKs are enabled again.

Foreign keys are disabled on logical replication subscribers.

Hi Peter,

Thanks for answer.

Just to clarify, they are disabled during initial sync only or are always disabled on subscriber side?
Are all triggers disabled during initial sync or just foreign keys?
How can I know that initial sync completed for all tables?  Is it checking when pg_subscription_rel.srsubstate is 'i'
or'd' for all tables or there is a better way?
 

Thanks!





-----Original Message-----
From: Peter Eisentraut [mailto:peter.eisentraut@enterprisedb.com]
Sent: Tuesday, December 7, 2021 7:19 AM
To: Avi Weinberg <AviW@gilat.com>; pgsql-general@lists.postgresql.org
Subject: Re: Are Foreign Key Disabled During Logical Replication Initial Sync?

On 06.12.21 15:50, Avi Weinberg wrote:
> Does it mean that populating each table is done in a single
> transaction?  If so, when I have tables with foreign keys between
> them, is it guaranteed that logical replication will populates the
> tables in the proper order so the foreign key will be enforced?  Or
> maybe the foreign keys are disabled during initial sync and only after
> all tables are populated the FKs are enabled again.

Foreign keys are disabled on logical replication subscribers.

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information
whichis confidential or privileged. If you are not the intended recipient, please inform the sender immediately and
deletethis email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.
 

On 07.12.21 08:51, Avi Weinberg wrote:
> Just to clarify, they are disabled during initial sync only or are always disabled on subscriber side?
> Are all triggers disabled during initial sync or just foreign keys?

All triggers are by default disabled on replicas.  See the ALTER TABLE 
clauses DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER to change this.

> How can I know that initial sync completed for all tables?  Is it checking when pg_subscription_rel.srsubstate is 'i'
or'd' for all tables or there is a better way?
 

There are various ways to phrase this.  The test suite often uses this 
query:

SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN 
('r', 's');