Thread: [GENERAL] CREATE FOREIGN TABLE difficulties
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
On 06/15/2017 09:49 AM, Paul Lavoie wrote: > 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 goinginto the child database shows no signs of any tables whatsoever. I am assuming you are doing the above on the parent database. CREATE FOREIGN TABLE does not actually create the table on the remote(child), it has to exist there already. It creates the table on parent and links it to the table on the remote(child).: https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html You will also want to pay attention to: "If a schema name is given (for example, CREATE FOREIGN TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. The name of the foreign table must be distinct from the name of any other foreign table, table, sequence, index, view, or materialized view in the same schema." from the above link. > > This is under Postgresql 9.6.3, with the software being tested on servers running MacOS, NetBSD/amd64, & Solaris/x86_64(various combinations). I’ve tried removing the schema qualifications, redoing the host as properly remoterather than localhost, removing the port number, all without any signs of success. Oddly, the inverse of IMPORT FOREIGNSCHEMA appears to 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 possibilityI’m missing the obvious, I’d thought I’d try here first. > > I’m going to go try the 10.0 beta now… > > Thanks! > > - Paul > -- Adrian Klaver adrian.klaver@aklaver.com
The ‘does not actually create’ the table was the piece I was missing. I wasn’t sure either way from reading the documentationfor the command, but makes sense from a perspective of trying to keep the databases independent. One of the reasons I went down this path was the hopes to not need to manipulate the table definitions in each of the databases,rather make the definition once and let it propagate to the children as necessary, courtesy of the ‘INHERITS’. I’ll adjust my plans accordingly. Thanks for the reply! - Paul > On Jun 15, 2017, at 1:27 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 06/15/2017 09:49 AM, Paul Lavoie wrote: >> 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 goinginto the child database shows no signs of any tables whatsoever. > > I am assuming you are doing the above on the parent database. > CREATE FOREIGN TABLE does not actually create the table on the remote(child), it has to exist there already. It createsthe table on parent and links it to the table on the remote(child).: > > https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html > > You will also want to pay attention to: > > "If a schema name is given (for example, CREATE FOREIGN TABLE myschema.mytable ...) then the table is created in the specifiedschema. Otherwise it is created in the current schema. The name of the foreign table must be distinct from the nameof any other foreign table, table, sequence, index, view, or materialized view in the same schema." > > > from the above link. > >> This is under Postgresql 9.6.3, with the software being tested on servers running MacOS, NetBSD/amd64, & Solaris/x86_64(various combinations). I’ve tried removing the schema qualifications, redoing the host as properly remoterather than localhost, removing the port number, all without any signs of success. Oddly, the inverse of IMPORT FOREIGNSCHEMA appears to work. >> I’m particularly curious as to how one would troubleshoot this scenario. I’m somewhat surprised at the CREATE FOREIGNTABLE returning success when it doesn’t appear to have done all the work - the Postgreql instance must be present,but it doesn’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 possibilityI’m missing the obvious, I’d thought I’d try here first. >> I’m going to go try the 10.0 beta now… >> Thanks! >> - Paul > > > -- > Adrian Klaver > adrian.klaver@aklaver.com