Hi Hackers, add_foreign_final_paths() currently disables pushing down FETCH FIRST .. WITH TIES entirely, because doing so requires knowing whether the remote server is v13+ (which added support for the clause), and checking that would mean opening a connection during planning (see the discussion at https://postgr.es/m/[email protected] which led to the current behavior).
Attached patch fills in that one remaining gap. postgres_fdw already keeps a connection cache alive for the session's lifetime; if a connection to the relevant foreign server already exists in that cache (from an earlier query in the same session), its version is known for free, with no additional network access. GetCachedConnectionVersion() lookup into that cache and retun cached version. This information used in add_foreign_final_paths() to allow the pushdown only when a cached connection reports version 13 or later. The relation's server/user mapping are read from RelOptInfo's own serverid/userid fields, which are InvalidOid whenever the relation spans more than one foreign server (a cross-server join, or a sharded partitioned table). So the pushdown correctly stays disabled in those cases. appendLimitClause() is updated to emit the SQL-standard FETCH FIRST clause (with OFFSET ahead of it, per the grammar) instead of plain LIMIT/OFFSET when WITH TIES is in use. The value in that position is parsed as c_expr rather than a_expr, which does not accept the "::type" cast decoration deparseExpr() normally emits for constants; the patch parenthesizes it, which c_expr explicitly allows. Regarding the collation/tie-semantics concern raised in the original thread: by the time add_foreign_final_paths() runs, ORDER BY has already been determined safe to push down by an earlier check. Ties are just rows that compare equal under that same, already-vetted comparison. So no new risk is introduced by additionallyfetching the tied rows. Tested against a loopback foreign server, including: 1/ cold-cache sessions correctly falling back to local evaluation; 2/ warm-cache sessions pushing the FETCH clause down with results matching the non-FDW reference, both with and without OFFSET 3/ cross-server joins/unions correctly never attempting the pushdown. New regression tests added to postgres_fdw.sql/expected covering all of the above. make check passes. Regards, Sagar Shedge Multigres Engineer, Supabase
0001-postgres_fdw-fetch-first-with-ties.patch
Description: Binary data
