postgres_fdw - push down conditionals for ENUMs
Hi
It's well known that pushing down of WHERE conditions supported for
built-in data types, operators and functions only.
So if your main table has columns declared with custom domain (CREATE
DOMAIN ID_TYPE AS INT NOT NULL) and you want just to proxy all queries over
FDW foreign table needs to be declared as INT. This approach works fine for
any domain based on build-in type. But ENUM is a special case. When I
declare foreign table with TEXT column it accepts all queries like SELECT *
FROM proxy WHERE status = 'active', but 'active' pushed down with explicit
type cast 'active'::text and this creates a problem because origin server
expects ENUM value ('active' or 'active'::STATUS_TYPE) rather than TEXT.
CREATE CAST (TEXT as STATUS_TYPE) WITH function to_status_type(text) AS
IMPLICIT;
All attempts to cast text to ENUM type were unsuccessful (probably because
PostgreSQL converts ENUM values to INTs on query rewriting stage, but
casting works later, when data accessed):
CREATE CAST (STATUS_TYPE AS TEXT) WITH INOUT AS IMPLICIT;
Casting in reverse direction works fine, but this dirty trick forces
PostgreSQL to convert ENUMs to TEXT (which is less optimal as working
internally with INTs) for absolutely all requests, not only forwarded over
FDW.
Questions to developers:
1. Is there any plans to add "non-strict mode" (configurable via options on
server/table/column level) to allow pushing down conditions for all data
types?
2. There is an option that allows to map foreign table column to column
with another name. What about adding another option to specify column type
to be send to remote server?
Thanks.
Tested on 9.3.4
--
Sergiy Zuban
Sergiy Zuban <s.zuban@gmail.com> writes:
1. Is there any plans to add "non-strict mode" (configurable via options on
server/table/column level) to allow pushing down conditions for all data
types?
No. You might as well call it a "return random answers" mode.
2. There is an option that allows to map foreign table column to column
with another name. What about adding another option to specify column type
to be send to remote server?
Same problem. We don't have any way of knowing whether type foo on the
remote end acts like foo locally.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
1. Is there any plans to add "non-strict mode" (configurable via options
on
server/table/column level) to allow pushing down conditions for all data
types?No. You might as well call it a "return random answers" mode.
Its bad. I think most users would be happy to have "auto discovery" mode
when foreign table fetches all required meta info to act like original
table.
2. There is an option that allows to map foreign table column to column
with another name. What about adding another option to specify columntype
to be send to remote server?
Same problem. We don't have any way of knowing whether type foo on the
remote end acts like foo locally
I understand it breaks all logic how FDW works internally, but I'm still
trying to find some workaround to allow pushing down conditions for enums.
CREATE CAST (TEXT as STATUS_TYPE) WITH function to_status_type(text) AS
IMPLICIT;
Could you please confirm such cast won't work because PostgreSQL converts
ENUM values to INTs (enumtypid) on query rewriting stage, but casting works
later, when data accessed?
I was thinking about looking up "enumtypid" in pg_enum by "enumlabel", but
I couldn't find any way to force PostgreSQL to somehow use found enumtypid
instead of original text.
1. Is there any plans to add "non-strict mode" (configurable via options
onserver/table/column level) to allow pushing down conditions for all data
types?No. You might as well call it a "return random answers" mode.
Its bad. I think most users would be happy to have "auto discovery" mode
when foreign table fetches all required meta info to act like original
table.
Since 9.5 has IMPORT FOREIGN SCHEMA don't you think that foreign tables can
be marked as "imported". This 100% guarantees that all user-defined data
types/domains (including ENUMs) imported as well. It should be safe to push
down conditionals for imported types. Does it makes sense?