Operators missing from push down config

[rascalDan]

Following an upgrade from 2.8.0 to 2.9.0, we noticed an issue selecting a small subset of data from a large foreign table. Table is approximately 245 million records, we're looking at selecting a few 10s of thousands. The query is loosely speaking:

select * from foreign_table where id >= ? and id < ?

but the query executed by MariaDB was:

select * from foreign_table where id < ?

In the cases causing a problem, the bound parameter was roughly the last id value in the table, resulting in MariaDB creating a temporary file containing the entire table and filling the tmp volume. I've had a look through the changes between 2.8 and 2.9 and I believe the problem lies in mysql_fdw_pushdown.config which is missing operator>=(bigint,bigint).

[surajkharage19]

Hi @rascalDan,

It seems you have correctly identified an issue. You need to add the required entries in mysql_fdw_pushdown.config file. e.g. OPERATOR pg_catalog.>=(bigint, bigint) Once that is added you can reload the config file using select mysql_fdw_display_pushdown_list(true); command or start a new session so that new entries can take effect. Users can add/modify/delete the entries as per their requirements from mysql_fdw_pushdown.config file.

Please let us know if above suggestion works.

[rascalDan]

Thanks, I'll see if I can arrange getting the upgrade back in place with a modified configuration.

[drm]

This issue also causes JOINs not to be pushed down in case of BIGINT primary/foreign keys, because pg_catalog.=(bigint,bigint) is also missing. I'll reference this in a PR.

[drm]

Any thoughts on how to one-off-generate an exhaustive list of supported push-down operators in order to avoid human error?

[surajkharage19]

Hi @drm,

The main purpose of the configuration file is to give an option to the user to decide which operators/functions can be pushed down to the remote server. We do not have any automated way to populate configuration file entries but you can refer to the instructions given at the start of the configuration file which will help you to generate configuration file entries correctly as per your requirement. Also, we are planning to add more default entries in the configuration file in the upcoming release.

[marklynch]

I've added a PR that adds the missing bigint values here: https://github.com/EnterpriseDB/mysql_fdw/pull/276

[surajkharage19]

Hi,

Since we have added more default entries in config file which will serve your purpose, closing this ticket from our end.

[marklynch]

Thanks for getting the pushed through and released. 🙏