Re: postgres_fdw does not see enums

On Wed, Dec 03, 2014 at 05:52:03PM -0500, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate