PR for Pushdown of LIMIT and OFFSET

[abramsh]

Would you be open to a PR that adds pushdown of LIMIT and OFFSET?

I just read #71 and generally agree with the limitations and suggested generator workaround. However, at least for us, there are many queries which can be completely pushed down other than LIMIT.

Obviously, the logic would need to confirm that the query could be completely pushed down, which means in addition to a new Python method to check if the FDW can support limit/offset, we'd also need a new method to check if it supports the full set of quals.

I'd already started an implementation before I saw #71, but I won't bother going any further if you have no interest in merging it.

[mfenniak]

Would you be open to a PR that adds pushdown of LIMIT and OFFSET?

I'd absolutely be interested in such a PR, but I'd have one caveat. We'd need to build confidence that the concerns documented in #71 aren't going to appear quietly and break queries unexpectedly. So:

• Situations would need to be included in the automated test suite, so that this doesn't regress in future PG version changes
• As you described, we'd need to make sure the query can be completely pushed down
• I'd have special concerns for JOIN conditions with the FDW, where it might be possible that multicorn says "I can fully push this down!" without recognizing that other conditions in the query that aren't related to the table could cause subtle breakage.

So, basically yes, as long as it's well tested and that testing is incorporated into the regression test suite. 🙂 I think this would be great, yes!

[luss]

I agree with Mathieu. This is a kewl feature if we do it right AND it can be validated through automated testing. IMHO, if this came out as a feature sooner rather than later. It would be worth a 3.2 release tag rather than waiting for next year in the normal Postgres release cycle.

…

On Tue, Sep 2, 2025 at 11:45 AM Mathieu Fenniak ***@***.***> wrote: *mfenniak* left a comment (pgsql-io/multicorn2#81) <#81 (comment)> Would you be open to a PR that adds pushdown of LIMIT and OFFSET? I'd absolutely be interested in such a PR, but I'd have one caveat. We'd need to build confidence that the concerns documented in #71 <#71> aren't going to appear quietly and break queries unexpectedly. So: - Situations would need to be included in the automated test suite, so that this doesn't regress in future PG version changes - As you described, we'd need to make sure the query can be completely pushed down - I'd have special concerns for JOIN conditions with the FDW, where it *might* be possible that multicorn says "I can fully push this down!" without recognizing that other conditions in the query that aren't related to the table could cause subtle breakage. So, basically yes, as long as it's well tested and that testing is incorporated into the regression test suite. 🙂 I think this would be great, yes! — Reply to this email directly, view it on GitHub <#81 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHSBUYWGW753I3OE3QL3QW3QDAVCNFSM6AAAAACFNUIBZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTENBVHA4DGOBSGM> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[abramsh]

I'm not sure how quickly this will come unless someone else wants to help. It all depends on how much time I can devote to it. I have some basics working, and all the existing tests pass, but I still have to write all the new tests and make sure they pass.

[abramsh]

I've created a draft PR (#82) that implements the basics but is still missing a few checks.

I'm not a Postgres Planner/FDW expert, so I thought it would make sense to get early feedback as well as suggestions on additional tests I should add. I know I need to add some non-pushdown where clauses, but what else?

[luss]

nice!! Anxious to hear the feedback from Mathieu and others more technically qualified than me.

…

On Wed, Sep 3, 2025 at 3:55 PM Howard Abrams ***@***.***> wrote: *abramsh* left a comment (pgsql-io/multicorn2#81) <#81 (comment)> I've created a draft PR (#82 <#82>) that implements the basics but is still missing a few checks. I'm not a Postgres Planner/FDW expert, so I thought it would make sense to get early feedback as well as suggestions on additional tests I should add. I know I need to add some non-pushdown where clauses, but what else? — Reply to this email directly, view it on GitHub <#81 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHXIK3H6BP4USA26HVD3Q5BRNAVCNFSM6AAAAACFNUIBZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTENJQGU3TCNBRGU> . You are receiving this because you commented.Message ID: ***@***.***>

[luss]

Congrats to Howard on PR#82 getting pushed. I bumped up the development version number, of the project, from 3.1 to 3.2. Seems that I broke the tests by bumping the version number but I assume that someone more technical than me can fix that quickly. :-)

[luss]

Maybe we tag as 3.2dev1 once the regression tests pass again.

[mfenniak]

@luss There was just another version reference in flake.nix that was missed, fixed in #83.

[abramsh]

What's the plan on releasing this and 3.2?

[luss]

Good question. If we are feature complete and reasonably tested right now I see no reason not to cut a `v3.2` tag. I personally, hope we are able to push down LIMIT and OFFSET in more situations like for simple where clauses, but, enhancements can wait for future releases. Unless Mathieu objects, I'll doa release.

…

On Tue, Sep 30, 2025 at 5:43 PM Howard Abrams ***@***.***> wrote: *abramsh* left a comment (pgsql-io/multicorn2#81) <#81 (comment)> What's the plan on releasing this and 3.2? — Reply to this email directly, view it on GitHub <#81 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHRZ4OQFW6IQWQFLNBD3VL2PRAVCNFSM6AAAAACFNUIBZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTGNJTHA4TQOJSGA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[abramsh]

While you two are working that out, I've posted a proposal (#90) for push down limit in more situations.

[mfenniak]

@luss I'd agree with doing a release, whenever you are able.

[luss]

will do later today, v3.2 lives w Howard's new feature

…

On Thu, Oct 9, 2025 at 10:17 PM Mathieu Fenniak ***@***.***> wrote: *mfenniak* left a comment (pgsql-io/multicorn2#81) <#81 (comment)> @luss <https://github.com/luss> I'd agree with doing a release, whenever you are able. — Reply to this email directly, view it on GitHub <#81 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHQMECUYTG7R6YV7GCL3W4JK5AVCNFSM6AAAAACFNUIBZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTGOBYGA2DQOJWGY> . You are receiving this because you were mentioned.Message ID: ***@***.***>