Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8405d5a

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Refuse to send FETCH FIRST WITH TIES to remote servers.
Previously, when considering LIMIT pushdown, postgres_fdw failed tocheck whether the query has this clause, which led to pushing falseLIMIT clauses, causing incorrect results.This clause has been supported since v13, so we need to do aremote-version check before deciding that it will be safe to push such aclause, but we do not currently have a way to do the check (withoutaccessing the remote server); disable pushing such a clause for now.Oversight in commit357889e. Back-patch to v13, where that commitadded the support.Per bug #18467 from Onder Kalaci.Patch by Japin Li, per a suggestion from Tom Lane, with some changes tothe comments by me. Review by Onder Kalaci, Alvaro Herrera, and me.Discussion:https://postgr.es/m/18467-7bb89084ff03a08d%40postgresql.org
1 parentd9ff92c commit8405d5a

File tree

3 files changed

+40
-0
lines changed

3 files changed

+40
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1047,6 +1047,27 @@ SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
10471047
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
10481048
(1 row)
10491049

1050+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
1051+
EXPLAIN (VERBOSE, COSTS OFF)
1052+
SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
1053+
QUERY PLAN
1054+
-------------------------------------------------------------------------------------------------
1055+
Limit
1056+
Output: c2
1057+
-> Foreign Scan on public.ft1 t1
1058+
Output: c2
1059+
Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE (("C 1" > 960)) ORDER BY c2 ASC NULLS LAST
1060+
(5 rows)
1061+
1062+
SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
1063+
c2
1064+
----
1065+
0
1066+
0
1067+
0
1068+
0
1069+
(4 rows)
1070+
10501071
-- Test CASE pushdown
10511072
EXPLAIN (VERBOSE, COSTS OFF)
10521073
SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1;

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7045,6 +7045,20 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
70457045
if (ifpinfo->local_conds)
70467046
return;
70477047

7048+
/*
7049+
* If the query has FETCH FIRST .. WITH TIES, 1) it must have ORDER BY as
7050+
* well, which is used to determine which additional rows tie for the last
7051+
* place in the result set, and 2) ORDER BY must already have been
7052+
* determined to be safe to push down before we get here. So in that case
7053+
* the FETCH clause is safe to push down with ORDER BY if the remote
7054+
* server is v13 or later, but if not, the remote query will fail entirely
7055+
* for lack of support for it. Since we do not currently have a way to do
7056+
* a remote-version check (without accessing the remote server), disable
7057+
* pushing the FETCH clause for now.
7058+
*/
7059+
if (parse->limitOption==LIMIT_OPTION_WITH_TIES)
7060+
return;
7061+
70487062
/*
70497063
* Also, the LIMIT/OFFSET cannot be pushed down, if their expressions are
70507064
* not safe to remote.

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -408,6 +408,11 @@ EXPLAIN (VERBOSE, COSTS OFF)
408408
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
409409
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
410410

411+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
412+
EXPLAIN (VERBOSE, COSTS OFF)
413+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
414+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
415+
411416
-- Test CASE pushdown
412417
EXPLAIN (VERBOSE, COSTS OFF)
413418
SELECT c1,c2,c3FROM ft2WHERE CASE WHEN c1>990 THEN c1 END<1000ORDER BY c1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp