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

Commit2b461ef

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 parent8d072fe commit2b461ef

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
@@ -1038,6 +1038,27 @@ SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1;
10381038
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
10391039
(1 row)
10401040

1041+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
1042+
EXPLAIN (VERBOSE, COSTS OFF)
1043+
SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
1044+
QUERY PLAN
1045+
-------------------------------------------------------------------------------------------------
1046+
Limit
1047+
Output: c2
1048+
-> Foreign Scan on public.ft1 t1
1049+
Output: c2
1050+
Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE (("C 1" > 960)) ORDER BY c2 ASC NULLS LAST
1051+
(5 rows)
1052+
1053+
SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
1054+
c2
1055+
----
1056+
0
1057+
0
1058+
0
1059+
0
1060+
(4 rows)
1061+
10411062
-- check schema-qualification of regconfig constant
10421063
CREATE TEXT SEARCH CONFIGURATION public.custom_search
10431064
(COPY = pg_catalog.english);

‎contrib/postgres_fdw/postgres_fdw.c

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

6341+
/*
6342+
* If the query has FETCH FIRST .. WITH TIES, 1) it must have ORDER BY as
6343+
* well, which is used to determine which additional rows tie for the last
6344+
* place in the result set, and 2) ORDER BY must already have been
6345+
* determined to be safe to push down before we get here. So in that case
6346+
* the FETCH clause is safe to push down with ORDER BY if the remote
6347+
* server is v13 or later, but if not, the remote query will fail entirely
6348+
* for lack of support for it. Since we do not currently have a way to do
6349+
* a remote-version check (without accessing the remote server), disable
6350+
* pushing the FETCH clause for now.
6351+
*/
6352+
if (parse->limitOption==LIMIT_OPTION_WITH_TIES)
6353+
return;
6354+
63416355
/*
63426356
* Also, the LIMIT/OFFSET cannot be pushed down, if their expressions are
63436357
* 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
@@ -392,6 +392,11 @@ EXPLAIN (VERBOSE, COSTS OFF)
392392
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
393393
SELECT*FROM ft1 t1WHEREt1.c1===t1.c2order byt1.c2limit1;
394394

395+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
396+
EXPLAIN (VERBOSE, COSTS OFF)
397+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
398+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
399+
395400
-- check schema-qualification of regconfig constant
396401
CREATETEXT SEARCH CONFIGURATIONpublic.custom_search
397402
(COPY=pg_catalog.english);

0 commit comments

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