Hi,
Long time user, new poster…
For assorted reasons I’ve decided to shard a database across multiple instances of postgresql running on the same
machine.I’ve set up a lot of children servers with a ‘fdw’ user to work with the foreign data wrapper and created the
childdatabase along with a schema, and then on the main database go thru the process of:
CREATE SCHEMA myschema;
CREATE TABLE mytable (mycol TEXT);
CREATE EXTENSION postgres_fdw;
CREATE SERVER db001 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ‘localhost’, port ‘8001’, dbname = ‘db001’);
CREATE USER MAPPING FOR myuser SERVER db001 OPTIONS (user ‘fdw’, password ‘XXX’);
CREATE FOREIGN TABLE myschema.mytable () INHERITS (mytable) SERVER db001 OPTIONS (schema_name ‘myschema’, table_name
‘mytable’);
Attempts to SELECT from myschema.mytable then fail with a “relation “myschema.mytable” does not exist” error, and going
intothe child database shows no signs of any tables whatsoever.
This is under Postgresql 9.6.3, with the software being tested on servers running MacOS, NetBSD/amd64, & Solaris/x86_64
(variouscombinations). I’ve tried removing the schema qualifications, redoing the host as properly remote rather than
localhost,removing the port number, all without any signs of success. Oddly, the inverse of IMPORT FOREIGN SCHEMA
appearsto work.
I’m particularly curious as to how one would troubleshoot this scenario. I’m somewhat surprised at the CREATE FOREIGN
TABLEreturning success when it doesn’t appear to have done all the work - the Postgreql instance must be present, but
itdoesn’t complain if the database, never mind the schema, doesn’t exist.
If this turns out to be a bug, I’ll happily move to the bug mailing list to discuss further. But under the possibility
I’mmissing the obvious, I’d thought I’d try here first.
I’m going to go try the 10.0 beta now…
Thanks!
- Paul
