Re: [Postgres-xc-general] ERROR: Cannot create foreign key whose evaluation cannot be enforced to remote nodes

[木 幸市 <ko...@in...>]

Yes, this is current XC limitation.  In all these cases, we need to visit another datanode to enforce these reference integrities, which is not provided generally yet.

Example of the workaround is:

> ALTER TABLE ONLY cookbook_versions
>     ADD CONSTRAINT cookbook_versions_cookbook_id_fkey FOREIGN KEY (cookbook_id) REFERENCES cookbooks(id) ON DELETE RESTRICT;

1. Change cookbooks distribution to "replication", or
2. cookbook_versions.cookbook_id and cookbooks.id are distribution key.

Please note that both case are enforced just locally in each datanode.

BTW, I'm finding many similar usecase and this has higher priority in the future development.

Regards;
---
Koichi Suzuki

On 2013/08/22, at 8:49, Darío Ezequiel Nievas <dar...@me...> wrote:

> Hi Guys.
> I could really use your help. 
> I'm a sysadmin. I'm not a DBA, and I don't have any experience with PostgreSQL.
> 
> I'm trying PGXC as an HA solution for the PGSQL DB Component in our Chef infrastructure.
> The problem I'm currently having, is that when I try to import the schema provided by Opscode, I get the following error:
> 
> ERROR:  Cannot create foreign key whose evaluation cannot be enforced to remote nodes
> 
> I think I'm getting the error on the following statements
> --
> -- Name: cookbook_version_checksums_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
> --
> 
> ALTER TABLE ONLY cookbook_version_checksums
>     ADD CONSTRAINT cookbook_version_checksums_org_id_fkey FOREIGN KEY (org_id, checksum) REFERENCES checksums(org_id, checksum) ON UPDATE CASCADE ON DELETE RESTRICT;
> 
> 
> --
> -- Name: cookbook_versions_cookbook_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
> --
> 
> ALTER TABLE ONLY cookbook_versions
>     ADD CONSTRAINT cookbook_versions_cookbook_id_fkey FOREIGN KEY (cookbook_id) REFERENCES cookbooks(id) ON DELETE RESTRICT;
> 
> 
> --
> -- Name: data_bag_items_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
> --
> 
> ALTER TABLE ONLY data_bag_items
>     ADD CONSTRAINT data_bag_items_org_id_fkey FOREIGN KEY (org_id, data_bag_name) REFERENCES data_bags(org_id, name) ON UPDATE CASCADE ON DELETE CASCADE;
> 
> 
> 
> Is there a way to work around this? Or is there a limitation in PGXC for this scenario?
> 
> I'm attaching the pgsql_schema.sql for reference
> 
> Thanks in advance!
> Dario Nievas
> 
> 
> <pgsql_schema.sql>------------------------------------------------------------------------------
> Introducing Performance Central, a new site from SourceForge and 
> AppDynamics. Performance Central is your source for news, insights, 
> analysis and resources for efficient Application Performance Management. 
> Visit us today!
> http://pubads.g.doubleclick.net/gampad/clk?id=48897511&iu=/4140/ostg.clktrk_______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general