Incorrect LIMIT pushdown since 2.4

[fbattke]

We have found some constellations where LIMIT clauses are incorrectly pushed down to oracle before the WHERE clause is applied, leading to empty resultsets where results should have been found.

A prototypical example:

FOREIGN TABLE oracle_table ( id INT, oracle_value TEXT); TABLE postgres_table1(id INT, id2 INT, postgres_value1 INT); TABLE postgres_table2(id2 INT, postgres_value2 INT);

SELECT * FROM postgres_table1 JOIN oracle_table USING (id) JOIN postgres_table2 USING (id2) -- join against postgres_table1 WHERE oracle_table.oracle_value='A' AND postgres_table2.postgres_value2=5 LIMIT 1;

Limit  (cost=10000.83..10013.91 rows=1 width=52)
   ->  Nested Loop  (cost=10000.83..10013.91 rows=1 width=52)
         ->  Nested Loop  (cost=10000.42..10013.44 rows=1 width=20)
               ->  Foreign Scan on oracle_table  (cost=10000.00..10010.00 rows=1 width=10)
                     Oracle query: SELECT /*65d552e5673a17e3*/ r2."id", r2."oracle_value" FROM "oracle_table" r2 WHERE (r2."oracle_value" = 'A') 
                       FETCH FIRST 1 ROWS ONLY
               ->  Index Scan using postgrs_table1_id on postgres_table1  (cost=0.42..3.44 rows=1 width=20)
                     Index Cond: (id = (oracle_table.id)::integer)
         ->  Index Scan using postgres_table2_id on postgres_table2  (cost=0.42..0.46 rows=1 width=10)
               Index Cond: (id2 = postgres_table1.id2)
               Filter: (postgres_value2 = 5::integer)

A workaround seems to be to adding an ORDER BY clause:

SELECT * FROM postgres_table1 JOIN oracle_table USING (id) JOIN postgres_table2 USING (id2) -- join against postgres_table1 WHERE oracle_table.oracle_value='A' AND postgres_table2.postgres_value2=5 ORDER BY id LIMIT 1;

This issue was introduced with 2.4 and is present in 2.5. We haven't yet have time to check 2.6, but from the git history I don't see an obvious change that would address it.

[laurenz]

Thanks for the report; this is indeed a bug. Could you check if my patch fixes your problem?

[fbattke]

Thanks, that was super quick! We currently have no test instance with postgres >=13.10, so we can't install the 2.6 release of oracle_fdw. It may take a bit longer to get the patch tested, unfortunately.

[laurenz]

I see, thanks for the feedback. It is dangerous to be running an old minor release of PostgreSQL.

[fbattke]

Well, it is a test instance. Our production instance runs on current PostgreSQL. Unfortunately, we just learned that it is sometimes dangerous to run a current release of oracle_fdw in production. You're never 100% safe ;)

[laurenz]

I try to keep Git HEAD in good shape, but nothing is perfect. Releases don't receive any special testing; I just slap on a tag and write release notes when a PostgreSQL major release is approaching.

[fbattke]

Good to know! That makes me much more comfortable with using the patch in production as a replacement for a "real" release. Until now, I would only have used releases in production.

And it never hurts saying it: This is a really great tool and indeed the HEAD is in very good shape.

[fbattke]

I have now tested e70be51 and the issue is fixed in my test case.

[laurenz]

Thanks for the feedback!