pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Japin Li <japinli@hotmail.com>
Subject: [PATCH v2] Push down FETCH FIRST WITH TIES to the remote side
Date: Thu, 16 May 2024 17:42:53 +0800
---
contrib/postgres_fdw/deparse.c | 13 +-
.../postgres_fdw/expected/postgres_fdw.out | 113 ++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 2 +
src/backend/parser/gram.y | 2 +
4 files changed, 128 insertions(+), 2 deletions(-)
inline patch 1: contrib/postgres_fdw/deparse.c, contrib/postgres_fdw/expected/postgres_fdw.out, contrib/postgres_fdw/sql/postgres_fdw.sql and 1 more (+128/-2 lines) download inline-1.patch diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index fb590c87e6..a95fc2527f 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -4012,8 +4012,17 @@ appendLimitClause(deparse_expr_cxt *context)
if (root->parse->limitCount)
{
- appendStringInfoString(buf, " LIMIT ");
- deparseExpr((Expr *) root->parse->limitCount, context);
+ if (root->parse->limitOption == LIMIT_OPTION_WITH_TIES)
+ {
+ appendStringInfoString(buf, " FETCH FIRST ");
+ deparseExpr((Expr *) root->parse->limitCount, context);
+ appendStringInfoString(buf, " ROWS WITH TIES");
+ }
+ else
+ {
+ appendStringInfoString(buf, " LIMIT ");
+ deparseExpr((Expr *) root->parse->limitCount, context);
+ }
}
if (root->parse->limitOffset)
{
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 078b8a966f..d350d1bdf4 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -339,6 +339,119 @@ SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
(110,0,00110,"Sun Jan 11 00:00:00 1970 PST","Sun Jan 11 00:00:00 1970",0,"0 ",foo)
(10 rows)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY c2 ASC NULLS LAST FETCH FIRST 2::bigint ROWS WITH TIES
+(3 rows)
+
+SELECT * FROM ft1 t1 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+------+----+-------+------------------------------+--------------------------+----+------------+-----
+ 1000 | 0 | 01000 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 200 | 0 | 00200 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 190 | 0 | 00190 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 180 | 0 | 00180 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 170 | 0 | 00170 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 160 | 0 | 00160 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 150 | 0 | 00150 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 140 | 0 | 00140 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 130 | 0 | 00130 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 110 | 0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 920 | 0 | 00920 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 120 | 0 | 00120 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 910 | 0 | 00910 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 900 | 0 | 00900 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 890 | 0 | 00890 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 880 | 0 | 00880 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 870 | 0 | 00870 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 860 | 0 | 00860 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 850 | 0 | 00850 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 20 | 0 | 00020 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 990 | 0 | 00990 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 840 | 0 | 00840 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 830 | 0 | 00830 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 820 | 0 | 00820 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 810 | 0 | 00810 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 800 | 0 | 00800 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 790 | 0 | 00790 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 780 | 0 | 00780 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 770 | 0 | 00770 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 30 | 0 | 00030 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 980 | 0 | 00980 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 760 | 0 | 00760 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 750 | 0 | 00750 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 740 | 0 | 00740 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 730 | 0 | 00730 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 720 | 0 | 00720 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 710 | 0 | 00710 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 700 | 0 | 00700 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 690 | 0 | 00690 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 40 | 0 | 00040 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 970 | 0 | 00970 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 680 | 0 | 00680 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 670 | 0 | 00670 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 660 | 0 | 00660 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 650 | 0 | 00650 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 640 | 0 | 00640 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 630 | 0 | 00630 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 620 | 0 | 00620 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 610 | 0 | 00610 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 50 | 0 | 00050 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 960 | 0 | 00960 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 600 | 0 | 00600 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 590 | 0 | 00590 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 580 | 0 | 00580 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 570 | 0 | 00570 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 560 | 0 | 00560 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 550 | 0 | 00550 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 540 | 0 | 00540 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 530 | 0 | 00530 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 60 | 0 | 00060 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 950 | 0 | 00950 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 520 | 0 | 00520 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 510 | 0 | 00510 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 500 | 0 | 00500 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 490 | 0 | 00490 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 480 | 0 | 00480 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 470 | 0 | 00470 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 460 | 0 | 00460 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 450 | 0 | 00450 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 70 | 0 | 00070 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 940 | 0 | 00940 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 440 | 0 | 00440 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 430 | 0 | 00430 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 420 | 0 | 00420 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 410 | 0 | 00410 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 400 | 0 | 00400 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 390 | 0 | 00390 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 380 | 0 | 00380 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 370 | 0 | 00370 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 80 | 0 | 00080 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 930 | 0 | 00930 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 360 | 0 | 00360 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 350 | 0 | 00350 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 340 | 0 | 00340 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 330 | 0 | 00330 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 320 | 0 | 00320 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 310 | 0 | 00310 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 300 | 0 | 00300 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 290 | 0 | 00290 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 90 | 0 | 00090 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 10 | 0 | 00010 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 280 | 0 | 00280 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+ 270 | 0 | 00270 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 260 | 0 | 00260 | Mon Mar 02 00:00:00 1970 PST | Mon Mar 02 00:00:00 1970 | 0 | 0 | foo
+ 250 | 0 | 00250 | Fri Feb 20 00:00:00 1970 PST | Fri Feb 20 00:00:00 1970 | 0 | 0 | foo
+ 240 | 0 | 00240 | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
+ 230 | 0 | 00230 | Sat Jan 31 00:00:00 1970 PST | Sat Jan 31 00:00:00 1970 | 0 | 0 | foo
+ 220 | 0 | 00220 | Wed Jan 21 00:00:00 1970 PST | Wed Jan 21 00:00:00 1970 | 0 | 0 | foo
+ 210 | 0 | 00210 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo
+ 100 | 0 | 00100 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+(100 rows)
+
-- empty result
SELECT * FROM ft1 WHERE false;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 09ba234e43..ca8778d00d 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -265,6 +265,8 @@ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
-- whole-row reference
EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+SELECT * FROM ft1 t1 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
-- empty result
SELECT * FROM ft1 WHERE false;
-- with WHERE clause
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4d582950b7..4494410fec 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -13292,6 +13292,8 @@ select_offset_value:
*/
select_fetch_first_value:
c_expr { $$ = $1; }
+ | a_expr TYPECAST Typename
+ { $$ = makeTypeCast($1, $3, @2); }
| '+' I_or_F_const
{ $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, "+", NULL, $2, @1); }
| '-' I_or_F_const
--
2.41.0
Message-ID: <no-message-id-25538@localhost>
Permalink: ../no-message-id-25538@localhost/
Also on: postgresql.org/message-id/no-message-id-25538@localhost
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: japinli@hotmail.com
Subject: Re: [PATCH v2] Push down FETCH FIRST WITH TIES to the remote side
In-Reply-To: <no-message-id-25538@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox