SQL: error when creating a foreign table that has an enum column

Question

I'm creating a foreign table (foo_table) in database_a. foo_table lives in database_b. foo_table has an enum (bar_type) as one of its columns. Because this enum is in database_b, the creation of the foreign table fails in database_a. database_a doesn't understand the column type. Running the following in database_a

CREATE FOREIGN TABLE foo_table (id integer NOT NULL, bar bar_type) SERVER database_b

One gets the error:

ERROR: type "bar_type" does not exist

I could just create a copy of bar_type in database_a, but this feels duplicative and possibly a future cause of inconsistency. Would anyone have thoughts on best practices for handling?

Authoritarian

Answer 1

I summarize the answer I received from the pgsql-general mailing list:

1. A foreign table is basically an ad-hoc remote data source for the local database, so the onus is on the local database to maintain its definition of the remote table, whether it's in another (or even the same) PostgreSQL server or a completely different data source, especially as the local definition can be different from the remote one.
2. This does mean that there's no simple way of ensuring any remote dependencies are present on the local server. PostgreSQL 9.5 will provide the IMPORT FOREIGN SCHEMA command, however this is limited to table/view definitions.
3. If the definition of enum becomes inconsistant, we'd expect errors to occur when retrieving rows that had values not known on the local side.
4. One potential way around this is to declare the foreign table's columns as "text" rather than enums; you would lose some error checking on the local side, but the remote server would enforce validity whenever you stored something.
5. However, not clear if this hack would to behave desirably for WHERE conditions on the enum column. I will be checking the performance for WHERE conditions and will update this answer when I have more details.

All credit goes to the fine persons on the pgsql-general mailing list.

Authoritarian

To point 2, see this interesting thread postgresql.org/message-id/20141203213139.GA9855%40fetter.org . It helped me understand the context of such decision. – Singularize

Answer 2

You can create a script to transfer the enums by making this query and then create them on your server:

SELECT format(
          'CREATE TYPE %s AS ENUM (%s);',
          enumtypid::regtype,
          string_agg(quote_literal(enumlabel), ', ')
       )
FROM pg_enum
GROUP BY enumtypid;

Thanks @laurenz-albe Postgres how to transfer all enums from foreign server

Massorete

Answer 3

In a real pinch (and when you don't have too many enums/domains). It works to manually create each one in the new local foreign schema before running the import into it.

In my case I'm using postgres_fdw for rare, high volume migrations between databases that benefit from running purely in SQL instead of passing through an application server somewhere.

Since we only do it occasionally, and have Postgres enums numbering in the 10s, not 100s, this workaround is feasible to keep the performance benefit of running the migrations on the Postgres server.

Cribb