Обсуждение: BUG #17889: Invalid cursor direction for a foreign scan that reached the fetch_size (MOVE BACKWARD ALL IN cX)
BUG #17889: Invalid cursor direction for a foreign scan that reached the fetch_size (MOVE BACKWARD ALL IN cX)
От
PG Bug reporting form
Дата:
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'
;
On Fri, Apr 7, 2023 at 5:21 AM PG Bug reporting form <noreply@postgresql.org> wrote: > 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 Will look into this. Thanks for the report! Best regards, Etsuro Fujita
I CCed Tom.
On Fri, Apr 7, 2023 at 6:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Fri, Apr 7, 2023 at 5:21 AM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > 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
>
> Will look into this.
Here is a simple reproducer:
create server loopback foreign data wrapper postgres_fdw options
(dbname 'postgres');
create user mapping for current_user server loopback;
create table loc1 (a int, b text);
create table loc2 (a int, b text);
insert into loc1 select i, 'loc1' from generate_series(1, 1000) i;
insert into loc2 select i, 'loc2' from generate_series(1, 2) i;
create foreign table rem1 (a int, b text) server loopback options
(table_name 'loc1');
analyze rem1;
analyze loc2;
set enable_mergejoin to false;
set enable_hashjoin to false;
set enable_material to false;
explain verbose update rem1 set b = rem1.b || rem1.b from loc2 where
rem1.a = loc2.a and random() > 0.0;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on public.rem1 (cost=100.00..308.02 rows=0 width=0)
Remote SQL: UPDATE public.loc1 SET b = $2 WHERE ctid = $1
-> Nested Loop (cost=100.00..308.02 rows=1 width=77)
Output: (rem1.b || rem1.b), rem1.ctid, rem1.*, loc2.ctid
Join Filter: ((rem1.a = loc2.a) AND (random() > '0'::double precision))
-> Seq Scan on public.loc2 (cost=0.00..1.02 rows=2 width=10)
Output: loc2.ctid, loc2.a
-> Foreign Scan on public.rem1 (cost=100.00..136.00
rows=1000 width=48)
Output: rem1.b, rem1.ctid, rem1.*, rem1.a
Remote SQL: SELECT a, b, ctid FROM public.loc1 FOR UPDATE
(10 rows)
update rem1 set b = rem1.b || rem1.b from loc2 where rem1.a = loc2.a
and random() > 0.0;
ERROR: cursor can only scan forward
HINT: Declare it with SCROLL option to enable backward scan.
CONTEXT: remote SQL command: MOVE BACKWARD ALL IN c1
I think that the root cause is in commit d844cd75a, which disallowed
rewinding and then re-fetching forwards in a NO SCROLL cursor. I am
not sure what to do about this issue, but I am wondering whether that
commit is too restrictive, because 1) these examples would work just
fine without that commit, and 2) we still allow
rewind-and-fetch-forwards in a SCROLL cursor even when the query
includes volatile functions.
Best regards,
Etsuro Fujita
I resent this, because I forgot to CC it to Tom. :( On Sat, Apr 29, 2023 at 8:36 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > > I CCed Tom. > > On Fri, Apr 7, 2023 at 6:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > > On Fri, Apr 7, 2023 at 5:21 AM PG Bug reporting form > > <noreply@postgresql.org> wrote: > > > 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 > > > > Will look into this. > > Here is a simple reproducer: > > create server loopback foreign data wrapper postgres_fdw options > (dbname 'postgres'); > create user mapping for current_user server loopback; > create table loc1 (a int, b text); > create table loc2 (a int, b text); > insert into loc1 select i, 'loc1' from generate_series(1, 1000) i; > insert into loc2 select i, 'loc2' from generate_series(1, 2) i; > create foreign table rem1 (a int, b text) server loopback options > (table_name 'loc1'); > analyze rem1; > analyze loc2; > set enable_mergejoin to false; > set enable_hashjoin to false; > set enable_material to false; > > explain verbose update rem1 set b = rem1.b || rem1.b from loc2 where > rem1.a = loc2.a and random() > 0.0; > QUERY PLAN > ----------------------------------------------------------------------------------- > Update on public.rem1 (cost=100.00..308.02 rows=0 width=0) > Remote SQL: UPDATE public.loc1 SET b = $2 WHERE ctid = $1 > -> Nested Loop (cost=100.00..308.02 rows=1 width=77) > Output: (rem1.b || rem1.b), rem1.ctid, rem1.*, loc2.ctid > Join Filter: ((rem1.a = loc2.a) AND (random() > '0'::double precision)) > -> Seq Scan on public.loc2 (cost=0.00..1.02 rows=2 width=10) > Output: loc2.ctid, loc2.a > -> Foreign Scan on public.rem1 (cost=100.00..136.00 > rows=1000 width=48) > Output: rem1.b, rem1.ctid, rem1.*, rem1.a > Remote SQL: SELECT a, b, ctid FROM public.loc1 FOR UPDATE > (10 rows) > > update rem1 set b = rem1.b || rem1.b from loc2 where rem1.a = loc2.a > and random() > 0.0; > ERROR: cursor can only scan forward > HINT: Declare it with SCROLL option to enable backward scan. > CONTEXT: remote SQL command: MOVE BACKWARD ALL IN c1 > > I think that the root cause is in commit d844cd75a, which disallowed > rewinding and then re-fetching forwards in a NO SCROLL cursor. I am > not sure what to do about this issue, but I am wondering whether that > commit is too restrictive, because 1) these examples would work just > fine without that commit, and 2) we still allow > rewind-and-fetch-forwards in a SCROLL cursor even when the query > includes volatile functions. > > Best regards, > Etsuro Fujita
Etsuro Fujita <etsuro.fujita@gmail.com> writes:
>> I think that the root cause is in commit d844cd75a, which disallowed
>> rewinding and then re-fetching forwards in a NO SCROLL cursor. I am
>> not sure what to do about this issue, but I am wondering whether that
>> commit is too restrictive, because 1) these examples would work just
>> fine without that commit, and 2) we still allow
>> rewind-and-fetch-forwards in a SCROLL cursor even when the query
>> includes volatile functions.
Well, the short answer here is that postgres_fdw is depending on
something that has squishy semantics, for the reasons enumerated
in d844cd75a (see also [1]). Maybe we can carve out an exception
that's narrow enough that we can convince ourselves it's not squishy,
but I'm not very sure what the rules should be.
regards, tom lane
[1] https://www.postgresql.org/message-id/201144.1682350828%40sss.pgh.pa.us