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

Dian Fay <dian.m.fay@gmail.com>

From: "Dian M Fay" <dian.m.fay@gmail.com>
To: <pgsql-hackers@postgresql.org>
Date: 2021-03-01T07:24:01Z
Lists: pgsql-hackers

Attachments

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.

create database alpha;
create database beta;

\c alpha

create type itemtype as enum ('one', 'two', 'three');
create table items (
  id serial not null primary key,
  type itemtype not null
);
insert into items (type) values ('one'), ('one'), ('two');

\c beta

create extension postgres_fdw;
create server alpha foreign data wrapper postgres_fdw options (dbname 'alpha', host 'localhost', port '5432');
create user mapping for postgres server alpha options (user 'postgres');

create foreign table alpha_items (
  id int,
  type text
) server alpha options (table_name 'items');
select * from alpha_items; -- ok
select * from alpha_items where type = 'one';

ERROR:  operator does not exist: public.itemtype = text
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  remote SQL command: SELECT id, type FROM public.items WHERE ((type = 'one'::text))

The attached changeset adds a new boolean option for postgres_fdw
foreign table columns, `use_local_type`. When true, ColumnRefs for the
relevant attribute will be deparsed with a cast to the type defined in
`CREATE FOREIGN TABLE`.

create foreign table alpha_items (
  id int,
  type text options (use_local_type 'true')
) server alpha options (table_name 'items');
select * from alpha_items where type = 'one'; -- succeeds

This builds and checks, with a new regression test and documentation.

Commits

  1. postgres_fdw: suppress casts on constants in limited cases.