diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 45 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 20 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 25 |
3 files changed, 85 insertions, 5 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 0cc77190dc4..8f0886f2ff6 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6853,6 +6853,51 @@ SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; 40 | 42 | 00040_trig_update | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo (10 rows) +-- Test ReScan code path that recreates the cursor even when no parameters +-- change (bug #17889) +CREATE TABLE loct1 (c1 int); +CREATE TABLE loct2 (c1 int, c2 text); +INSERT INTO loct1 VALUES (1001); +INSERT INTO loct1 VALUES (1002); +INSERT INTO loct2 SELECT id, to_char(id, 'FM0000') FROM generate_series(1, 1000) id; +INSERT INTO loct2 VALUES (1001, 'foo'); +INSERT INTO loct2 VALUES (1002, 'bar'); +CREATE FOREIGN TABLE remt2 (c1 int, c2 text) SERVER loopback OPTIONS (table_name 'loct2'); +ANALYZE loct1; +ANALYZE remt2; +SET enable_mergejoin TO false; +SET enable_hashjoin TO false; +SET enable_material TO false; +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; + QUERY PLAN +-------------------------------------------------------------------------------- + Update on public.remt2 + Output: remt2.c1, remt2.c2 + Remote SQL: UPDATE public.loct2 SET c2 = $2 WHERE ctid = $1 RETURNING c1, c2 + -> Nested Loop + Output: (remt2.c2 || remt2.c2), remt2.ctid, remt2.*, loct1.ctid + Join Filter: (remt2.c1 = loct1.c1) + -> Seq Scan on public.loct1 + Output: loct1.ctid, loct1.c1 + -> Foreign Scan on public.remt2 + Output: remt2.c2, remt2.ctid, remt2.*, remt2.c1 + Remote SQL: SELECT c1, c2, ctid FROM public.loct2 FOR UPDATE +(11 rows) + +UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; + c1 | c2 +------+-------- + 1001 | foofoo + 1002 | barbar +(2 rows) + +RESET enable_mergejoin; +RESET enable_hashjoin; +RESET enable_material; +DROP FOREIGN TABLE remt2; +DROP TABLE loct1; +DROP TABLE loct2; -- =================================================================== -- test check constraints -- =================================================================== diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 0bb9a5ae8f6..fc65d81e217 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1662,9 +1662,12 @@ postgresReScanForeignScan(ForeignScanState *node) /* * If any internal parameters affecting this node have changed, we'd - * better destroy and recreate the cursor. Otherwise, rewinding it should - * be good enough. If we've only fetched zero or one batch, we needn't - * even rewind the cursor, just rescan what we have. + * better destroy and recreate the cursor. Otherwise, if the remote + * server is v14 or older, rewinding it should be good enough; if not, + * rewind is only allowed for scrollable cursors, but we don't have a way + * to check the scrollability of it, so destroy and recreate it in any + * case. If we've only fetched zero or one batch, we needn't even rewind + * the cursor, just rescan what we have. */ if (node->ss.ps.chgParam != NULL) { @@ -1674,8 +1677,15 @@ postgresReScanForeignScan(ForeignScanState *node) } else if (fsstate->fetch_ct_2 > 1) { - snprintf(sql, sizeof(sql), "MOVE BACKWARD ALL IN c%u", - fsstate->cursor_number); + if (PQserverVersion(fsstate->conn) < 150000) + snprintf(sql, sizeof(sql), "MOVE BACKWARD ALL IN c%u", + fsstate->cursor_number); + else + { + fsstate->cursor_exists = false; + snprintf(sql, sizeof(sql), "CLOSE c%u", + fsstate->cursor_number); + } } else { diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index b57f8cfda68..733c1037712 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1647,6 +1647,31 @@ SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10; EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10; +-- Test ReScan code path that recreates the cursor even when no parameters +-- change (bug #17889) +CREATE TABLE loct1 (c1 int); +CREATE TABLE loct2 (c1 int, c2 text); +INSERT INTO loct1 VALUES (1001); +INSERT INTO loct1 VALUES (1002); +INSERT INTO loct2 SELECT id, to_char(id, 'FM0000') FROM generate_series(1, 1000) id; +INSERT INTO loct2 VALUES (1001, 'foo'); +INSERT INTO loct2 VALUES (1002, 'bar'); +CREATE FOREIGN TABLE remt2 (c1 int, c2 text) SERVER loopback OPTIONS (table_name 'loct2'); +ANALYZE loct1; +ANALYZE remt2; +SET enable_mergejoin TO false; +SET enable_hashjoin TO false; +SET enable_material TO false; +EXPLAIN (VERBOSE, COSTS OFF) +UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; +UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*; +RESET enable_mergejoin; +RESET enable_hashjoin; +RESET enable_material; +DROP FOREIGN TABLE remt2; +DROP TABLE loct1; +DROP TABLE loct2; + -- =================================================================== -- test check constraints -- =================================================================== |