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

Commitb33c141

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 parent00071ef commitb33c141

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
@@ -6839,6 +6839,20 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
68396839
if (ifpinfo->local_conds)
68406840
return;
68416841

6842+
/*
6843+
* If the query has FETCH FIRST .. WITH TIES, 1) it must have ORDER BY as
6844+
* well, which is used to determine which additional rows tie for the last
6845+
* place in the result set, and 2) ORDER BY must already have been
6846+
* determined to be safe to push down before we get here. So in that case
6847+
* the FETCH clause is safe to push down with ORDER BY if the remote
6848+
* server is v13 or later, but if not, the remote query will fail entirely
6849+
* for lack of support for it. Since we do not currently have a way to do
6850+
* a remote-version check (without accessing the remote server), disable
6851+
* pushing the FETCH clause for now.
6852+
*/
6853+
if (parse->limitOption==LIMIT_OPTION_WITH_TIES)
6854+
return;
6855+
68426856
/*
68436857
* Also, the LIMIT/OFFSET cannot be pushed down, if their expressions are
68446858
* 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
@@ -407,6 +407,11 @@ EXPLAIN (VERBOSE, COSTS OFF)
407407
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
408408
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
409409

410+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
411+
EXPLAIN (VERBOSE, COSTS OFF)
412+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
413+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
414+
410415
-- Test CASE pushdown
411416
EXPLAIN (VERBOSE, COSTS OFF)
412417
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