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
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
