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
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 |