BUG #17889: Invalid cursor direction for a foreign scan that reached the fetch_size (MOVE BACKWARD ALL IN cX)

The following bug has been logged on the website:

Bug reference: 17889
Logged by: Eric Cyr
Email address: eric(dot)cyr(at)gmail(dot)com
PostgreSQL version: 15.2
Operating system: Ubuntu 22.04.2 LTS, macOS 13.3
Description:

Hello,

I stumbled upon a strange case that causes an error in version 15.
Hopefully the following will be helpful.

Thank you very much
Have a great day

--

The scenario is the following:
On DB 1 have
3 tables, a view using a where clause on table 1 and a view using a inner
join on table 1 and 2.

On DB 2 have
A fdw server(with use_remote_estimate set to true), 3 foreign tables, one
for each view and one for table 3 and a view on the foreign table of view of
table 1 (with a cte using a function and used in where clause)

On DB 2 execute a SELECT on view of foreign table 1 with a join on foreign
table 2 with a where clause using a subquery on foreign table 3.

If the SELECT would return an amount of rows equal or greater than the fetch
size of foreign table of view of table 1 the error will occur.

--

The same scenario was tested on Postgres 10 and 14, both worked without
error.

--

The error is the following:

ERROR: cursor can only scan forward Hint:
Declare it with SCROLL option to enable backward scan.
Where: remote SQL command: MOVE BACKWARD ALL IN c3

--

The move backward happens in postgres_fdw.c 1676:1680

else if (fsstate->fetch_ct_2 > 1)
{
snprintf(sql, sizeof(sql), "MOVE BACKWARD ALL IN c%u",
fsstate->cursor_number);
}

--

The EXPLAIN result is the following:

Nested Loop Left Join (cost=224.47..923.78 rows=1 width=68)
" Output: a.def, b.other_def, a.c_fk"
Join Filter: (b.a_fk = a.pk)
-> Foreign Scan on schema_test.test_a_table_view_foreign a
(cost=100.00..773.45 rows=1 width=40)
" Output: a.pk, a.c_fk, a.def, a.flag"
Filter: ((SubPlan 1) = 'cdef1'::text)
" Remote SQL: SELECT pk, c_fk, def FROM
schema_test.test_a_table_view"
SubPlan 1
-> Foreign Scan on schema_test.test_c_table_foreign c
(cost=100.16..108.20 rows=1 width=32)
Output: c.def
Remote SQL: SELECT def FROM schema_test.test_c_table WHERE
((pk = $1::integer))
-> Foreign Scan on schema_test.test_b_table_view_foreign b
(cost=124.47..150.19 rows=6 width=44)
" Output: NULL::integer, b.a_fk, NULL::integer, b.other_def"
" Remote SQL: SELECT a_fk, other_def FROM
schema_test.test_b_table_view WHERE ((other_id = $1::integer))"
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.26 rows=1 width=4)
Output: schema_test.test_function()

--

Here is the SQL used to recreate the issue:

--

CREATE USER test_user PASSWORD '1234';
CREATE USER test_fdw_user PASSWORD '1234';

CREATE DATABASE db_test_1;
CREATE DATABASE db_test_2;

--
--
--

\c db_test_1

--

CREATE SCHEMA schema_test;
GRANT ALL ON SCHEMA schema_test TO public;

--

CREATE TABLE schema_test.test_c_table (
pk integer NOT NULL,
other_id integer,
def text NOT NULL,
flag integer NOT NULL
);

ALTER TABLE schema_test.test_c_table
ADD CONSTRAINT test_c_table_pk PRIMARY KEY (pk);

GRANT ALL ON TABLE schema_test.test_c_table TO public;

INSERT INTO schema_test.test_c_table(pk, other_id, def, flag) VALUES (1, 1,
'cdef1', 1);

--

CREATE TABLE schema_test.test_a_table (
pk integer NOT NULL,
c_fk integer NOT NULL,
def text,
flag integer NOT NULL
);

ALTER TABLE schema_test.test_a_table
ADD CONSTRAINT test_a_table_pk PRIMARY KEY (pk);

ALTER TABLE schema_test.test_a_table
ADD CONSTRAINT test_a_table_fk FOREIGN KEY (c_fk) REFERENCES
schema_test.test_c_table(pk);

GRANT ALL ON TABLE schema_test.test_a_table TO public;

INSERT INTO schema_test.test_a_table(pk, c_fk, def, flag) VALUES (101, 1,
'adef2_101', 1);
INSERT INTO schema_test.test_a_table(pk, c_fk, def, flag) VALUES (102, 1,
'adef2_102', 1);

--

CREATE VIEW schema_test.test_a_table_view
AS
SELECT
a.pk,
a.c_fk,
a.def,
a.flag
FROM schema_test.test_a_table a
WHERE a.flag = 1
;

GRANT ALL ON TABLE schema_test.test_a_table_view TO public;

--

CREATE TABLE schema_test.test_b_table (
pk integer NOT NULL,
a_fk integer NOT NULL,
other_id integer NOT NULL,
other_def text NOT NULL
);

ALTER TABLE schema_test.test_b_table
ADD CONSTRAINT test_b_table_pk PRIMARY KEY (pk);

ALTER TABLE schema_test.test_b_table
ADD CONSTRAINT test_b_table_fk FOREIGN KEY (a_fk) REFERENCES
schema_test.test_a_table(pk);

GRANT ALL ON TABLE schema_test.test_b_table TO public;

INSERT INTO schema_test.test_b_table(pk, a_fk, other_id, other_def) VALUES
(1011, 101, 1, 'bdef-101-1');
INSERT INTO schema_test.test_b_table(pk, a_fk, other_id, other_def) VALUES
(1012, 101, 2, 'bdef-101-2');
INSERT INTO schema_test.test_b_table(pk, a_fk, other_id, other_def) VALUES
(1021, 102, 1, 'bdef-102-1');
INSERT INTO schema_test.test_b_table(pk, a_fk, other_id, other_def) VALUES
(1022, 102, 2, 'bdef-102-2');

--

CREATE VIEW schema_test.test_b_table_view
AS
SELECT
b.pk,
b.a_fk,
b.other_id,
b.other_def
FROM schema_test.test_b_table b
INNER JOIN schema_test.test_a_table a
ON b.a_fk = a.pk
;

GRANT ALL ON TABLE schema_test.test_b_table_view TO public;

--
--
--

\c db_test_2

--

CREATE SCHEMA schema_test;
GRANT ALL ON SCHEMA schema_test TO public;

--

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER db_test_1_fdw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
dbname 'db_test_1',
host 'localhost',
use_remote_estimate 'true'
);

CREATE USER MAPPING FOR public SERVER db_test_1_fdw OPTIONS (
password '1234',
"user" 'test_fdw_user'
);

--

CREATE FOREIGN TABLE schema_test.test_a_table_view_foreign (
pk integer,
c_fk integer,
def text,
flag integer
)
SERVER db_test_1_fdw
OPTIONS (
schema_name 'schema_test',
table_name 'test_a_table_view'
);

--

CREATE FOREIGN TABLE schema_test.test_b_table_view_foreign (
pk integer,
a_fk integer,
other_id integer,
other_def text
)
SERVER db_test_1_fdw
OPTIONS (
schema_name 'schema_test',
table_name 'test_b_table_view',
fetch_size '100'
);

--

CREATE FUNCTION schema_test.test_function() RETURNS integer
LANGUAGE plpgsql STABLE
AS $$
BEGIN
return 1;
END;
$$
;

--

CREATE VIEW schema_test.test_b_table_view
AS
WITH t_test AS (
SELECT schema_test.test_function() AS other_id
)
SELECT
b.pk,
b.a_fk,
b.other_id,
b.other_def
FROM schema_test.test_b_table_view_foreign b
WHERE b.other_id = ( SELECT t.other_id FROM t_test t)
;

GRANT ALL ON TABLE schema_test.test_b_table_view TO public;

--

CREATE FOREIGN TABLE schema_test.test_c_table_foreign (
pk integer,
other_id integer,
def text,
flag integer
)
SERVER db_test_1_fdw
OPTIONS (
schema_name 'schema_test',
table_name 'test_c_table'
);

--
--
--

\c db_test_2

-- when nb returned rows >= fetch_size
-- fails with error -> [55000] ERROR: cursor can only scan forward Hint:
Declare it with SCROLL option to enable backward scan. Where: remote SQL
command: MOVE BACKWARD ALL IN c3

ALTER FOREIGN TABLE schema_test.test_b_table_view_foreign OPTIONS ( SET
fetch_size '2' );

SELECT a.def AS adef,
b.other_def AS bdef,
a.c_fk
FROM schema_test.test_a_table_view_foreign a
LEFT JOIN schema_test.test_b_table_view b
ON b.a_fk = a.pk
WHERE (SELECT c.def
FROM schema_test.test_c_table_foreign c
WHERE c.pk = a.c_fk) = 'cdef1'
;

-- when nb returned rows < fetch_size
-- succeed

ALTER FOREIGN TABLE schema_test.test_b_table_view_foreign OPTIONS ( SET
fetch_size '100' );

SELECT a.def AS adef,
b.other_def AS bdef,
a.c_fk
FROM schema_test.test_a_table_view_foreign a
LEFT JOIN schema_test.test_b_table_view b
ON b.a_fk = a.pk
WHERE (SELECT c.def
FROM schema_test.test_c_table_foreign c
WHERE c.pk = a.c_fk) = 'cdef1'
;