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

The following bug has been logged on the website:

Bug reference:      17889
Logged by:          Eric Cyr
Email address:      eric.cyr@gmail.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'
;