Re: [PATCH] postgres-fdw: column option to override foreign types

On Tue Mar 2, 2021 at 6:50 AM EST, Ashutosh Bapat wrote:
> On Mon, Mar 1, 2021 at 12:59 PM Dian M Fay <dian(dot)m(dot)fay(at)gmail(dot)com> wrote:
> >
> > Full use of a custom data type with postgres_fdw currently requires the
> > type be maintained in both the local and remote databases. `CREATE
> > FOREIGN TABLE` does not check declared types against the remote table,
> > but declaring e.g. a remote enum to be local text works only partway, as
> > seen here. A simple select query against alpha_items returns the enum
> > values as text; however, *filtering* on the column yields an error.
>
> postgres_fdw assumes that the local type declared is semantically same
> as the remote type. Ideally the enum should also be declared locally
> and used to declare type's datatype. See how to handle UDTs in
> postgres_fdw at
> https://stackoverflow.com/questions/37734170/can-the-foreign-data-wrapper-fdw-postgres-handle-the-geometry-data-type-of-postg

I'm aware, and the reason for this change is that I think it's annoying
to declare and maintain the type on the local server for the sole
purpose of accommodating a read-only foreign table that effectively
treats it like text anyway. The real scenario that prompted it is a
tickets table with status, priority, category, etc. enums. We don't have
plans to modify them any time soon, but if we do it's got to be
coordinated and deployed across two databases, all so we can use what
might as well be a text column in a single WHERE clause. Since foreign
tables can be defined over subsets of columns, reordered, and names
changed, a little opt-in flexibility with types too doesn't seem
misplaced.

Note that currently, postgres_fdw will strip casts on the WHERE column:
`where type::text = 'one'` becomes `where ((type = 'one'::text))` (the
value is cast separately). Making it respect those is another option,
but I thought including it in column configuration would be less
surprising to users who aren't aware of the difference between the local
and remote tables.