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

Commit5c571a3

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Avoid "cursor can only scan forward" error.
Commitd844cd7 disallowed rewind in a non-scrollable cursor to resolveanomalies arising from such a cursor operation. However, this failed totake into account the assumption in postgres_fdw that when rescanning aforeign relation, it can rewind the cursor created for scanning theforeign relation without specifying the SCROLL option, regardless of itsscrollability, causing this error when it tried to do such a rewind in anon-scrollable cursor. Fix by modifying postgres_fdw to insteadrecreate the cursor, regardless of its scrollability, when rescanningthe foreign relation. (If we had a way to check its scrollability, wecould improve this by rewinding it if it is scrollable and recreating itif not, but we do not have it, so this commit modifies it to recreate itin any case.)Per bug #17889 from Eric Cyr. Devrim Gunduz also reported this problem.Back-patch to v15 where that commit enforced the prohibition.Reviewed by Tom Lane.Discussion:https://postgr.es/m/17889-e8c39a251d258dda%40postgresql.orgDiscussion:https://postgr.es/m/b415ac3255f8352d1ea921cf3b7ba39e0587768a.camel%40gunduz.org
1 parentc145f32 commit5c571a3

File tree

3 files changed

+85
-5
lines changed

3 files changed

+85
-5
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6853,6 +6853,51 @@ SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
68536853
40 | 42 | 00040_trig_update | Tue Feb 10 00:00:00 1970 PST | Tue Feb 10 00:00:00 1970 | 0 | 0 | foo
68546854
(10 rows)
68556855

6856+
-- Test ReScan code path that recreates the cursor even when no parameters
6857+
-- change (bug #17889)
6858+
CREATE TABLE loct1 (c1 int);
6859+
CREATE TABLE loct2 (c1 int, c2 text);
6860+
INSERT INTO loct1 VALUES (1001);
6861+
INSERT INTO loct1 VALUES (1002);
6862+
INSERT INTO loct2 SELECT id, to_char(id, 'FM0000') FROM generate_series(1, 1000) id;
6863+
INSERT INTO loct2 VALUES (1001, 'foo');
6864+
INSERT INTO loct2 VALUES (1002, 'bar');
6865+
CREATE FOREIGN TABLE remt2 (c1 int, c2 text) SERVER loopback OPTIONS (table_name 'loct2');
6866+
ANALYZE loct1;
6867+
ANALYZE remt2;
6868+
SET enable_mergejoin TO false;
6869+
SET enable_hashjoin TO false;
6870+
SET enable_material TO false;
6871+
EXPLAIN (VERBOSE, COSTS OFF)
6872+
UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*;
6873+
QUERY PLAN
6874+
--------------------------------------------------------------------------------
6875+
Update on public.remt2
6876+
Output: remt2.c1, remt2.c2
6877+
Remote SQL: UPDATE public.loct2 SET c2 = $2 WHERE ctid = $1 RETURNING c1, c2
6878+
-> Nested Loop
6879+
Output: (remt2.c2 || remt2.c2), remt2.ctid, remt2.*, loct1.ctid
6880+
Join Filter: (remt2.c1 = loct1.c1)
6881+
-> Seq Scan on public.loct1
6882+
Output: loct1.ctid, loct1.c1
6883+
-> Foreign Scan on public.remt2
6884+
Output: remt2.c2, remt2.ctid, remt2.*, remt2.c1
6885+
Remote SQL: SELECT c1, c2, ctid FROM public.loct2 FOR UPDATE
6886+
(11 rows)
6887+
6888+
UPDATE remt2 SET c2 = remt2.c2 || remt2.c2 FROM loct1 WHERE loct1.c1 = remt2.c1 RETURNING remt2.*;
6889+
c1 | c2
6890+
------+--------
6891+
1001 | foofoo
6892+
1002 | barbar
6893+
(2 rows)
6894+
6895+
RESET enable_mergejoin;
6896+
RESET enable_hashjoin;
6897+
RESET enable_material;
6898+
DROP FOREIGN TABLE remt2;
6899+
DROP TABLE loct1;
6900+
DROP TABLE loct2;
68566901
-- ===================================================================
68576902
-- test check constraints
68586903
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1662,9 +1662,12 @@ postgresReScanForeignScan(ForeignScanState *node)
16621662

16631663
/*
16641664
* If any internal parameters affecting this node have changed, we'd
1665-
* better destroy and recreate the cursor. Otherwise, rewinding it should
1666-
* be good enough. If we've only fetched zero or one batch, we needn't
1667-
* even rewind the cursor, just rescan what we have.
1665+
* better destroy and recreate the cursor. Otherwise, if the remote
1666+
* server is v14 or older, rewinding it should be good enough; if not,
1667+
* rewind is only allowed for scrollable cursors, but we don't have a way
1668+
* to check the scrollability of it, so destroy and recreate it in any
1669+
* case. If we've only fetched zero or one batch, we needn't even rewind
1670+
* the cursor, just rescan what we have.
16681671
*/
16691672
if (node->ss.ps.chgParam!=NULL)
16701673
{
@@ -1674,8 +1677,15 @@ postgresReScanForeignScan(ForeignScanState *node)
16741677
}
16751678
elseif (fsstate->fetch_ct_2>1)
16761679
{
1677-
snprintf(sql,sizeof(sql),"MOVE BACKWARD ALL IN c%u",
1678-
fsstate->cursor_number);
1680+
if (PQserverVersion(fsstate->conn)<150000)
1681+
snprintf(sql,sizeof(sql),"MOVE BACKWARD ALL IN c%u",
1682+
fsstate->cursor_number);
1683+
else
1684+
{
1685+
fsstate->cursor_exists= false;
1686+
snprintf(sql,sizeof(sql),"CLOSE c%u",
1687+
fsstate->cursor_number);
1688+
}
16791689
}
16801690
else
16811691
{

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1647,6 +1647,31 @@ SELECT * FROM ft1 ORDER BY c6 DESC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
16471647
EXPLAIN (VERBOSE, COSTS OFF)SELECT*FROM ft1ORDER BY c6ASC NULLS FIRST, c1 OFFSET15LIMIT10;
16481648
SELECT*FROM ft1ORDER BY c6ASC NULLS FIRST, c1 OFFSET15LIMIT10;
16491649

1650+
-- Test ReScan code path that recreates the cursor even when no parameters
1651+
-- change (bug #17889)
1652+
CREATETABLEloct1 (c1int);
1653+
CREATETABLEloct2 (c1int, c2text);
1654+
INSERT INTO loct1VALUES (1001);
1655+
INSERT INTO loct1VALUES (1002);
1656+
INSERT INTO loct2SELECT id, to_char(id,'FM0000')FROM generate_series(1,1000) id;
1657+
INSERT INTO loct2VALUES (1001,'foo');
1658+
INSERT INTO loct2VALUES (1002,'bar');
1659+
CREATE FOREIGN TABLE remt2 (c1int, c2text) SERVER loopback OPTIONS (table_name'loct2');
1660+
ANALYZE loct1;
1661+
ANALYZE remt2;
1662+
SET enable_mergejoin TO false;
1663+
SET enable_hashjoin TO false;
1664+
SET enable_material TO false;
1665+
EXPLAIN (VERBOSE, COSTS OFF)
1666+
UPDATE remt2SET c2=remt2.c2||remt2.c2FROM loct1WHEREloct1.c1=remt2.c1 RETURNING remt2.*;
1667+
UPDATE remt2SET c2=remt2.c2||remt2.c2FROM loct1WHEREloct1.c1=remt2.c1 RETURNING remt2.*;
1668+
RESET enable_mergejoin;
1669+
RESET enable_hashjoin;
1670+
RESET enable_material;
1671+
DROP FOREIGN TABLE remt2;
1672+
DROPTABLE loct1;
1673+
DROPTABLE loct2;
1674+
16501675
-- ===================================================================
16511676
-- test check constraints
16521677
-- ===================================================================

0 commit comments

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