postgresql - unexpected plan node type error message when using cursor - Stack Overflow

admin管理员组

文章数量:1402837

I have a bit of code which checks whether any data was put into a refcursor. I've used this in many functions without any problems, but I have an issue in one function where postgres gave me an error saying that the cursor cannot scroll backwards. This is the basic logic I'm using all over the place:

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  -- If no rows exist, raise an error
  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
  MOVE BACKWARD 1 FROM ref; --if rows found move back to start of cursor to return all rows
  END IF;

What's weird though is that when I add the scroll option to the cursor I then get an error "ERROR: unexpected plan node type: 41". This is only in this one function, all the rest I'm using it in are fine.

I can't find anything on google about this error, apart from finding it in the source code. .html which seems to suggest it's an error creating a query plan.

I created a test function to try and isolate the issue (assuming it was something I've done) and it appears with this very simple function below.

CREATE OR REPLACE FUNCTION pg_temp.test_cursor()
 RETURNS refcursor
 LANGUAGE plpgsql
AS $$

DECLARE ref REFCURSOR;
has_rows BOOLEAN := FALSE;

BEGIN

ref := 'results_refcursor';
OPEN ref scroll FOR

SELECT 1 AS m;

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  -- If no rows exist, raise an error
  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
  MOVE BACKWARD 1 FROM ref; --if rows found move back to start of cursor to return all rows
  END IF;

RETURN ref;

END; $$;

Postgres version info: PostgreSQL 15.0 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), 64-bit

(You may be wondering why there's a cursor at all. I've already had this conversation, they're not changing it, so I just have to live with it)

I have a bit of code which checks whether any data was put into a refcursor. I've used this in many functions without any problems, but I have an issue in one function where postgres gave me an error saying that the cursor cannot scroll backwards. This is the basic logic I'm using all over the place:

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  -- If no rows exist, raise an error
  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
  MOVE BACKWARD 1 FROM ref; --if rows found move back to start of cursor to return all rows
  END IF;

What's weird though is that when I add the scroll option to the cursor I then get an error "ERROR: unexpected plan node type: 41". This is only in this one function, all the rest I'm using it in are fine.

I can't find anything on google about this error, apart from finding it in the source code. https://doxygen.postgresql./pl__exec_8c.html which seems to suggest it's an error creating a query plan.

I created a test function to try and isolate the issue (assuming it was something I've done) and it appears with this very simple function below.

CREATE OR REPLACE FUNCTION pg_temp.test_cursor()
 RETURNS refcursor
 LANGUAGE plpgsql
AS $$

DECLARE ref REFCURSOR;
has_rows BOOLEAN := FALSE;

BEGIN

ref := 'results_refcursor';
OPEN ref scroll FOR

SELECT 1 AS m;

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  -- If no rows exist, raise an error
  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
  MOVE BACKWARD 1 FROM ref; --if rows found move back to start of cursor to return all rows
  END IF;

RETURN ref;

END; $$;

Postgres version info: PostgreSQL 15.0 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), 64-bit

(You may be wondering why there's a cursor at all. I've already had this conversation, they're not changing it, so I just have to live with it)

• postgresql

Share Improve this question Follow edited Mar 21 at 13:22 DatabaseShouter asked Mar 21 at 11:17 DatabaseShouterDatabaseShouter 9471111 silver badges1313 bronze badges 2

• 1 Is this regular PostgreSQL or some fork? What is the exact version number? What is the exact code to create and open the cursor? Can you come up with a reproducible test case? This question lacks all the necessary information. – Laurenz Albe Commented Mar 21 at 11:36
• Thanks, I've added the Postgres version info. As for the exact, reproducible code, I had already added that temp function into the question, does that give you the same error if you create and run it? If not that would be interesting. – DatabaseShouter Commented Mar 21 at 13:24

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

This has been identified as a PostgreSQL bug, see the mailing list discussion. It will probably be fixed in the next minor release, due on 2025-05-08.

I found a workaround which was to use my query to insert into a temp table and then select from the temp table, which completed correctly.

本文标签： postgresqlunexpected plan node type error message when using cursorStack Overflow