LIMIT clause pushed to remote as is, leading to error

[AmebaBrain]

After creating foreign table and executing select * from my_foreign_table limit 1 getting error "Remote server returned an error". This happens if foreign table is pointed to the RDBS which doesn't support LIMIT clause for SELECT. For example, Oracle.

The reason, is because LIMIT clause is passed into the remote server as is. And if remote engine doesn't support such syntax - it results into an error.

For Oracle as a target

explain verbose
select id from my_foreign_table limit 1;

-- output
Foreign Scan on public.my_foreign_table o  (cost=1.00..1.00 rows=1 width=516)
  Output: id
  Remote SQL: SELECT id FROM my_foreign_table LIMIT 1

Here LIMIT 1 was passed directly to the oracle side. For fix, it's required to cast somehow LIMIT N to the WHERE ROWNUM <= N. Because obviously, you couldn't execute select id from my_foreign_table where rownum <= 1 on postgres side.

For quick check there is postgres docker image with jdbc_fdw installed: https://hub.docker.com/r/chumaky/postgres_jdbc_fdw

[JulmeTran]

Curently, JDBC FDW supports connecting PostgreSQL to Java DataBase Connectivity (JDBC) data source such as PostgreSQL, GridDB and MySQL. We have not yet tested using JDBC FDW to connect to Oracle data source.
And we will consider adding a mechanism in the future to adjust the LIMIT pushdown based on the target database.