postgreSQL:创建一个有enum列的外表时出现错误

Question

我正在database_a中创建一个外表(foo_table)。foo_table在database_b中。foo_table有一个enum (bar_type)作为其列之一。因为这个enum在database_b中，所以在database_a中创建外表失败。Database_a不理解列类型。在database_a

中运行以下命令

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

得到错误:

ERROR: type "bar_type" does not exist

我可以在database_a中创建bar_type的副本，但这感觉是重复的，并且可能会导致不一致。有人对处理的最佳实践有什么想法吗?

Answer 1

我总结一下我从pgsql-general邮件列表收到的答案:

1. 外表基本上是本地数据库的临时远程数据源，因此本地数据库有责任维护其远程表的定义，无论它是在另一个(甚至是相同的)PostgreSQL服务器上还是完全不同的数据源上，特别是当本地定义可能与远程定义不同时。
2. 这意味着没有简单的方法来确保本地服务器上存在任何远程依赖项。PostgreSQL 9.5将提供IMPORT FOREIGN SCHEMA命令，但是这仅限于表/视图定义。
3. 如果枚举的定义变得不一致，我们期望在检索具有本地未知值的行时发生错误。
一种可能的解决方法是将外表的列声明为"text"而不是枚举;您将在本地丢失一些错误检查，但是远程服务器将在您执行命令时强制执行有效性存储的东西。
4. 但是，不清楚这个hack是否会在enum列的WHERE条件下表现得很好。我将检查WHERE条件的性能，并在我有更多细节时更新此答案。

所有的功劳都归于pgsql-general邮件列表上的优秀人士。

Answer 2

您可以创建一个脚本，通过执行此查询来传输枚举，然后在您的服务器上创建它们:

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

谢谢@laurenz-albePostgres如何从外部服务器传输所有枚举

Answer 3

在真正的紧要关头(当您没有太多的枚举/域名时)。它的工作原理是在运行导入之前，在新的本地外部模式中手动创建每个外部模式。

在我的情况下，我使用postgres_fdw在数据库之间进行罕见的大容量迁移，这种迁移受益于纯粹运行SQL而不是通过某个应用服务器。

由于我们只是偶尔这样做，并且Postgres的枚举编号是10个，而不是100个，因此这种解决方法对于保持在Postgres服务器上运行迁移的性能优势是可行的。