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

Commit269e2c3

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 parent788c230 commit269e2c3

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

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

‎contrib/postgres_fdw/postgres_fdw.c‎

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

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

405+
-- Ensure we don't ship FETCH FIRST .. WITH TIES
406+
EXPLAIN (VERBOSE, COSTS OFF)
407+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
408+
SELECTt1.c2FROM ft1 t1WHEREt1.c1>960ORDER BYt1.c2 FETCH FIRST2 ROWS WITH TIES;
409+
405410
-- check schema-qualification of regconfig constant
406411
CREATETEXT SEARCH CONFIGURATIONpublic.custom_search
407412
(COPY=pg_catalog.english);

0 commit comments

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