Thread: postgres_fdw does not see enums
Folks, I've been trying out 9.5-to-be's PostgreSQL FDW, and I noticed that it doesn't seem to handle enum types. Would this be a trivial fix? Repro: Create an enum type in a 9.3 instance. Use the type in a table. Create a foreign server pointing to this instance. Execute IMPORT FOREIGN SCHEMA. Pseudonymized output: db_compare=# IMPORT FOREIGN SCHEMA public FROM SERVER blah INTO blah; ERROR: type "public.product_type" does not exist LINE 4: product_type public.product_type OPTIONS (column_name 'pro... ^ QUERY: CREATE FOREIGN TABLE foo ( id integer OPTIONS (column_name 'id') NOT NULL, event_id integer OPTIONS (column_name'event_id') NOT NULL, product_type public.product_type OPTIONS (column_name 'product_type') ) SERVER blah OPTIONS (schema_name 'public', table_name 'foo'); CONTEXT: importing foreign table "foo" Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> I've been trying out 9.5-to-be's PostgreSQL FDW, and I noticed that it
> doesn't seem to handle enum types. Would this be a trivial fix?
No. How would you know whether the remote side even has the enum,
let alone whether it has an identical set of members? I don't see
that enums are noticeably easier than the general case of non-built-in
types ...
regards, tom lane
On Wed, Dec 03, 2014 at 05:38:47PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I've been trying out 9.5-to-be's PostgreSQL FDW, and I noticed > > that it doesn't seem to handle enum types. Would this be a > > trivial fix? > > No. How would you know whether the remote side even has the enum, > let alone whether it has an identical set of members? I don't see > that enums are noticeably easier than the general case of > non-built-in types ... I must be missing something important. When querying the remote side, *and it's PostgreSQL*, we have catalog access that could be used to reconstruct the enums. Or are you thinking about the case where the enum changes from one call to the next? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Wed, Dec 03, 2014 at 05:38:47PM -0500, Tom Lane wrote:
>> No. How would you know whether the remote side even has the enum,
>> let alone whether it has an identical set of members? I don't see
>> that enums are noticeably easier than the general case of
>> non-built-in types ...
> I must be missing something important. When querying the remote side,
> *and it's PostgreSQL*, we have catalog access that could be used to
> reconstruct the enums. Or are you thinking about the case where the
> enum changes from one call to the next?
What do you mean "reconstruct the enum"? We can't fix inconsistencies
between the local enum definition and the remote definition (if any).
Say the remote has a value x that we don't, it'll fail when SELECTing
a row containing that value; postgres_fdw has no way to prevent such
a failure. Conversely, if we have a value y that doesn't exist on the
remote side, transmitting a clause "enumcol = 'y'" to the remote side
would fail. postgres_fdw has no way to prevent that, either, save not
transmitting clauses involving enums (which is exactly what it does now).
I suppose we could say that if you create a foreign-table definition
that includes an enum-type column, it's on your head that the enum
exists and is compatibly defined on the far end. Not sure about the
risk-benefit tradeoff here though. If you get it wrong (example: the
two servers have different opinions about the sort order of the enum's
values), you would end up with *very* hard to debug wrong results.
I'm not convinced that we really want to encourage users to do that.
regards, tom lane
On Wed, Dec 03, 2014 at 05:52:03PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Wed, Dec 03, 2014 at 05:38:47PM -0500, Tom Lane wrote: > >> No. How would you know whether the remote side even has the enum, > >> let alone whether it has an identical set of members? I don't see > >> that enums are noticeably easier than the general case of > >> non-built-in types ... > > > I must be missing something important. When querying the remote side, > > *and it's PostgreSQL*, we have catalog access that could be used to > > reconstruct the enums. Or are you thinking about the case where the > > enum changes from one call to the next? > > What do you mean "reconstruct the enum"? Capture its state at the time when IMPORT FOREIGN SCHEMA is executed. Right now, if you try IMPORT SCHEMA on a foreign table with an enum in it, postgresql_fdw errors out rather than trying to notice that there's an enum definition which should precede creation and execute it in the correct order. > We can't fix inconsistencies between the local enum definition and > the remote definition (if any). Your objection as stated applies to just about any ALTER issued on the remote side after the IMPORT FOREIGN SCHEMA has taken effect, not just to changes in enums. This is why I built functionality into DBI-Link that refreshes foreign tables. > Say the remote has a value x that we don't, it'll fail when > SELECTing a row containing that value; postgres_fdw has no way to > prevent such a failure. Conversely, if we have a value y that > doesn't exist on the remote side, transmitting a clause "enumcol = > 'y'" to the remote side would fail. postgres_fdw has no way to > prevent that, either, save not transmitting clauses involving enums > (which is exactly what it does now). > I suppose we could say that if you create a foreign-table definition > that includes an enum-type column, it's on your head that the enum > exists and is compatibly defined on the far end. We're already saying this about some substantial fraction of ALTER TABLEs that could happen on the remote side. I don't see how including enums could make it substantially worse. > Not sure about the risk-benefit tradeoff here though. If you get it > wrong (example: the two servers have different opinions about the > sort order of the enum's values), you would end up with *very* hard > to debug wrong results. I'm not convinced that we really want to > encourage users to do that. Perhaps we should add some compatibility checking functions for local vs. remote tables. The first cut of these could be, "are the tables defined identically up to what we've specified in the foreign server/foreign table stuff?" Subtler, looser versions might follow. For example, if the foreign table definition has VARCHAR(255) and the remote table has VARCHAR(100), it's not a catastrophe. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Wed, Dec 03, 2014 at 05:52:03PM -0500, Tom Lane wrote:
>> What do you mean "reconstruct the enum"?
> Capture its state at the time when IMPORT FOREIGN SCHEMA is executed.
> Right now, if you try IMPORT SCHEMA on a foreign table with an enum in
> it, postgresql_fdw errors out rather than trying to notice that
> there's an enum definition which should precede creation and execute
> it in the correct order.
Oh, you think IMPORT FOREIGN SCHEMA should try to import enums?
I doubt it. What happens if the enum already exists locally?
And why enums, and not domains, ranges, composite types, etc?
Perhaps more to the point, IMPORT FOREIGN SCHEMA is defined in the SQL
standard, as are its effects, and those effects are defined as a series of
CREATE FOREIGN TABLE commands. There's nothing there about trying to
import types that the tables might depend on.
regards, tom lane
On Wed, Dec 03, 2014 at 06:17:51PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Wed, Dec 03, 2014 at 05:52:03PM -0500, Tom Lane wrote: > >> What do you mean "reconstruct the enum"? > > > Capture its state at the time when IMPORT FOREIGN SCHEMA is executed. > > Right now, if you try IMPORT SCHEMA on a foreign table with an enum in > > it, postgresql_fdw errors out rather than trying to notice that > > there's an enum definition which should precede creation and execute > > it in the correct order. > > Oh, you think IMPORT FOREIGN SCHEMA should try to import enums? Yes. > I doubt it. What happens if the enum already exists locally? Informative error message along the lines of, "local enum foo.bar doesn't match remote enum public.bar" with a suitable HINT comparing the enums' values. However, I don't see much of a use case for this because INTO SCHEMA should be specifying an empty schema, or at least one without objects in it (like ENUMs) that could clash. > And why enums, and not domains, ranges, composite types, etc? You'd be assuming I think those should be excluded. ;) > Perhaps more to the point, IMPORT FOREIGN SCHEMA is defined in the > SQL standard, as are its effects, and those effects are defined as a > series of CREATE FOREIGN TABLE commands. There's nothing there > about trying to import types that the tables might depend on. The SQL standard has an awful lot of holes, this one being about the size of the Chicxulub crater. That fact doesn't force our implementation to throw up its hands when it finds a feature we've implemented and encouraged people to use. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, Dec 3, 2014 at 5:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Fetter <david@fetter.org> writes: >> On Wed, Dec 03, 2014 at 05:52:03PM -0500, Tom Lane wrote: >>> What do you mean "reconstruct the enum"? > >> Capture its state at the time when IMPORT FOREIGN SCHEMA is executed. >> Right now, if you try IMPORT SCHEMA on a foreign table with an enum in >> it, postgresql_fdw errors out rather than trying to notice that >> there's an enum definition which should precede creation and execute >> it in the correct order. > > Oh, you think IMPORT FOREIGN SCHEMA should try to import enums? > I doubt it. What happens if the enum already exists locally? > And why enums, and not domains, ranges, composite types, etc? Probably IMPORT FOREIGN SCHEMA should not attempt to include type dependencies. However, if they are present in the importer (that is, the type exists by name), it should assume that they are correct come what may. Something like 'IMPORT FOREIGN TYPE' would probably be needed to translate a type between servers. Unless the SQL standard has it or gets it I doubt it will ever appear but the status quo isn't too bad IMO. Personally I have no issues with the risks involved with type synchronizion; the problems faced are mostly academic with clean errors and easily managed unless binary format is used with postgres to postgres transfer (which IIRC the postgres fdw does not utilize at this time). User created types can't be transmitted between servers with the existing binary format; you have to transmit them as text and hope the structures agree. Binary format transmission in postgres tends to be quite a bit faster depending on the nature of the types involved, things like ints, numerics, and timestamps tend to be much faster. merlin