Is it safe to create foreign keys beforehand when logical replication is used?

From: Önder Kalacı <onderkalaci(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is it safe to create foreign keys beforehand when logical replication is used?
Date: 2017-10-23 13:28:43
Message-ID: CACawEhWkD55DSivuuMnZJqhYbAv6wR-eb7sRWD8msEVr=jdnvg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I'm trying to figure out whether the following is safe or not on all
conditions with logical replication:

-- on the source (localhost:5432), create the tables which have foreign
keys among them
-- load some data and create publication

CREATE TABLE rep_test (a int PRIMARY KEY);
CREATE TABLE rep_test_2 (a int REFERENCES rep_test);
INSERT INTO rep_test SELECT i FROM generate_series(0, 100000) i;
INSERT INTO rep_test_2 SELECT i FROM generate_series(0, 100000) i;
CREATE PUBLICATION pubtest FOR TABLE rep_test, rep_test_2;

-- on the target (localhost:9700) are the following stepscorrect?
-- create the foreign keys before the initial data load

CREATE TABLE rep_test (a int PRIMARY KEY);
CREATE TABLE rep_test_2 (a int REFERENCES rep_test);

-- later, create the subscription
CREATE SUBSCRIPTION testsub CONNECTION 'host=localhost port=5432
user=onderkalaci dbname=postgres' PUBLICATION pubtest;

According to my tests, I've never hit into any issues. But, I'm curious if
that's theoretically correct. Is there any case where the table which
references to another table's initial data load finishes earlier and the
referential integrity is broken?

Thanks,
Onder

Browse pgsql-general by date

  From Date Subject
Next Message Mark Lybarger 2017-10-23 14:08:04 multiple sql results to shell
Previous Message Önder Kalacı 2017-10-23 13:19:41 Re: A question on pg_stat_subscription view