Postgres Pro
Downloads
Home   >   mailing lists

Thread: [HACKERS] Logical replication failing when foreign key present

[HACKERS] Logical replication failing when foreign key present

From
Thom Brown
Date:
Hi,

There's an issue which I haven't seen documented as expected
behaviour, where replicating data to a table which has a foreign key
results in a replication failure.  This produces the following log
entries:

LOG:  starting logical replication worker for subscription "contacts_sub"
LOG:  logical replication apply for subscription "contacts_sub" has started
ERROR:  AfterTriggerSaveEvent() called outside of query
LOG:  worker process: logical replication worker for subscription
16408 (PID 19201) exited with exit code 1


Reproducible test case:

On both instances:
 CREATE TABLE b (bid int PRIMARY KEY); CREATE TABLE a (id int PRIMARY KEY, bid int REFERENCES b (bid)); INSERT INTO b
(bid)VALUES (1);
 

First instance: CREATE PUBLICATION a_pub FOR TABLE a;

Second instance: CREATE SUBSCRIPTION a_sub CONNECTION 'host=127.0.0.1 port=5530
user=thom dbname=postgres' PUBLICATION a_pub;

First instance:INSERT INTO a (id, bid) VALUES (1,1);


Regards

Thom

Re: [HACKERS] Logical replication failing when foreign key present

From
Petr Jelinek
Date:
On 22/01/17 18:50, Thom Brown wrote:
> Hi,
> 
> There's an issue which I haven't seen documented as expected
> behaviour, where replicating data to a table which has a foreign key
> results in a replication failure.  This produces the following log
> entries:
> 
> LOG:  starting logical replication worker for subscription "contacts_sub"
> LOG:  logical replication apply for subscription "contacts_sub" has started
> ERROR:  AfterTriggerSaveEvent() called outside of query
> LOG:  worker process: logical replication worker for subscription
> 16408 (PID 19201) exited with exit code 1
> 
> 

Hi, thanks for report.

Looks like I missed AfterTriggerBeginQuery/AfterTriggerEndQuery when
moving the executor stuff around. Attached should fix it.

-- 
  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachment

Re: [HACKERS] Logical replication failing when foreign key present

From
Peter Eisentraut
Date:
On 1/22/17 18:07, Petr Jelinek wrote:
> On 22/01/17 18:50, Thom Brown wrote:
>> There's an issue which I haven't seen documented as expected
>> behaviour, where replicating data to a table which has a foreign key
>> results in a replication failure.  This produces the following log
>> entries:

> Hi, thanks for report.
> 
> Looks like I missed AfterTriggerBeginQuery/AfterTriggerEndQuery when
> moving the executor stuff around. Attached should fix it.

This has been fixed.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services