[Postgres-xl-general] Reload Replicated Table?

Aaron Jackson ajackson at revionics.com
Mon Sep 22 06:46:26 PDT 2014

Actually this happened during an "alter table foo add node" sequence.  In this case, I had added a 4th datanode and then attempted to add it.  Doing so caused that to occur.

For the time being I exercised option 1 since a restore wasn't time consuming and the dump was already handy.  I will however, look into how to automatically generate the drop, load and recreation of the constraints.


From: Pavan Deolasee [pavan.deolasee at gmail.com]
Sent: Monday, September 22, 2014 5:51 AM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Reload Replicated Table?

Hi Aaron,

On Fri, Sep 19, 2014 at 12:26 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
Have a table of 28K items - so really not much - table is distributed by replication.  Added 3 data nodes and tried to redistribute the data to these new nodes.  Ended up with table truncation errors due to foreign key constraints.  My guess here is that a table managed by replication doesn't need to truncate at either the source or destination...

ERROR:  cannot truncate a table referenced in a foreign key constraint

I assume you got this error while deleting a node from replicated table, is that right? I could ADD  a node to a replicated table which is being referenced from another table. But I got same error like above while deleting the node. We can definitely do better than this if we could figure out that the referencing table is not located on the node where the referenced table is getting truncated.

My options appear to be

1. reload the table and dependencies
2. drop the constraint and redistribute then reset the constraint

Yeah, 2 seems like to a good option till we enhance XL to handle truncation of tables on nodes where they are not referenced by any other tables.

Pavan Deolasee
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-general-postgres-xl.org/attachments/20140922/d8691519/attachment.htm>

More information about the postgres-xl-general mailing list