Home > mailing lists

Thread: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

James Kitambara

Date:

09 December 2021, 13:36:34

ISSUE OF CURSOR ON THE EDB POSTGRESQL

I have the table books2 below with those fields on EDB PostgreSQL.

CREATE TABLE IF NOT EXISTS public.books2
(

id integer NOT NULL DEFAULT nextval('books2_id_seq'::regclass),

title character(10) COLLATE pg_catalog."default" NOT NULL,

amount numeric DEFAULT 0,

CONSTRAINT books2_pkey PRIMARY KEY (id)

);

The table is populated with the following data

I want to re-insert the records from ID 8 to 11 for the values of TITLE and AMOUNT as the ID is out-increment. To accomplish this I have created the procedure named temp_insert_in_books2() to do this

The procedure does what I wanted BUT IT GIVES ME THIS ERROR MESSAGE:

ERROR: cursor "book_cur" does not exist

CONTEXT: edb-spl function temp_insert_in_books2() line 15 at CLOSE

SQL state: 34000

HOW CAN I REMOVE THAT ERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ON THE TABLE USING CURSORS.

PLEASE CAN ANYONE ASSIST.

Table Data after running the procedure is described below:

Attachment

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

Sandeep Saxena

Date:

09 December 2021, 13:55:10

Do you have commit inside cursor?

On Thu, Dec 9, 2021 at 4:06 PM James Kitambara <jameskitambara@yahoo.co.uk> wrote:

ISSUE OF CURSOR ON THE EDB POSTGRESQL
I have the table books2 below with those fields on EDB PostgreSQL.
CREATE TABLE IF NOT EXISTS public.books2
(
    id integer NOT NULL DEFAULT nextval('books2_id_seq'::regclass),
    title character(10) COLLATE pg_catalog."default" NOT NULL,
    amount numeric DEFAULT 0,
    CONSTRAINT books2_pkey PRIMARY KEY (id)
);

The table is populated with the following data

I want to re-insert the records from ID 8 to 11 for the values of TITLE and AMOUNT as the ID is out-increment. To accomplish this I have created the procedure named temp_insert_in_books2() to do this
The procedure does what I wanted BUT IT GIVES ME THIS ERROR MESSAGE:
ERROR: cursor "book_cur" does not exist
CONTEXT: edb-spl function temp_insert_in_books2() line 15 at CLOSE
SQL state: 34000
HOW CAN I REMOVE THAT ERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ON THE TABLE USING CURSORS.
PLEASE CAN ANYONE ASSIST.

Table Data after running the procedure is described below:

Attachment

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

James Kitambara

Date:

10 December 2021, 18:40:41

There is no COMMIT in the loop for processing cursor data.

Sorry I forget to share the procedure on my first email:

Here is a procedure:

-------------------------------------------------------

CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2(

)

LANGUAGE 'edbspl'

SECURITY DEFINER VOLATILE PARALLEL UNSAFE

COST 100

AS $BODY$

--v_id INTEGER;

v_title CHAR(10);

v_amount NUMERIC;

CURSOR book_cur IS

SELECT title, amount FROM books2 WHERE id >=8;

BEGIN

OPEN book_cur;

LOOP

FETCH book_cur INTO v_title, v_amount;

EXIT WHEN book_cur%NOTFOUND;

INSERT INTO books2 (title, amount) VALUES (v_title, v_amount);

END LOOP;

COMMIT;

CLOSE book_cur;

END

$BODY$;

On Thursday, 9 December 2021, 13:55:31 GMT+3, Sandeep Saxena <sandeep.lko@gmail.com> wrote:

Do you have commit inside cursor?

On Thu, Dec 9, 2021 at 4:06 PM James Kitambara <jameskitambara@yahoo.co.uk> wrote:

ISSUE OF CURSOR ON THE EDB POSTGRESQL
I have the table books2 below with those fields on EDB PostgreSQL.
CREATE TABLE IF NOT EXISTS public.books2
(
    id integer NOT NULL DEFAULT nextval('books2_id_seq'::regclass),
    title character(10) COLLATE pg_catalog."default" NOT NULL,
    amount numeric DEFAULT 0,
    CONSTRAINT books2_pkey PRIMARY KEY (id)
);

The table is populated with the following data

I want to re-insert the records from ID 8 to 11 for the values of TITLE and AMOUNT as the ID is out-increment. To accomplish this I have created the procedure named temp_insert_in_books2() to do this
The procedure does what I wanted BUT IT GIVES ME THIS ERROR MESSAGE:
ERROR: cursor "book_cur" does not exist
CONTEXT: edb-spl function temp_insert_in_books2() line 15 at CLOSE
SQL state: 34000
HOW CAN I REMOVE THAT ERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ON THE TABLE USING CURSORS.
PLEASE CAN ANYONE ASSIST.

Table Data after running the procedure is described below:

Attachment

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

Simon Riggs

Date:

14 December 2021, 13:58:37

On Fri, 10 Dec 2021 at 15:40, James Kitambara
<jameskitambara@yahoo.co.uk> wrote:
>
> There is no COMMIT in the loop for processing cursor data.
>
> Sorry I forget to share the procedure on my first email:
>
> Here is a procedure:
> -------------------------------------------------------
>
> CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2(
> )
> LANGUAGE 'edbspl'
>     SECURITY DEFINER VOLATILE PARALLEL UNSAFE
>     COST 100
> AS $BODY$
>     --v_id         INTEGER;
>     v_title      CHAR(10);
> v_amount NUMERIC;
>     CURSOR book_cur IS
>         SELECT title, amount FROM books2 WHERE id >=8;
> BEGIN
>     OPEN book_cur;
>     LOOP
>         FETCH book_cur INTO v_title, v_amount;
>         EXIT WHEN book_cur%NOTFOUND;
> INSERT INTO books2 (title, amount) VALUES (v_title, v_amount);
>     END LOOP;
> COMMIT;
>     CLOSE book_cur;
> END
> $BODY$;

"Normally, cursors are automatically closed at transaction commit."
https://www.postgresql.org/docs/devel/plpgsql-transactions.html

So the explicit CLOSE is not needed, if you have the COMMIT.

But then why have the COMMIT? The transaction will commit by default,
so I would remove that.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

James Kitambara

Date:

14 December 2021, 18:22:03

Thank you Mr. Simon for your comment I will try that and see the results.

Kindly Regards,

James Kitambara

On Tuesday, 14 December 2021, 13:59:01 GMT+3, Simon Riggs <simon.riggs@enterprisedb.com> wrote:

On Fri, 10 Dec 2021 at 15:40, James Kitambara

<jameskitambara@yahoo.co.uk> wrote:
>
> There is no COMMIT in the loop for processing cursor data.
>
> Sorry I forget to share the procedure on my first email:
>
> Here is a procedure:
> -------------------------------------------------------
>
> CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2(
> )
> LANGUAGE 'edbspl'
> SECURITY DEFINER VOLATILE PARALLEL UNSAFE
> COST 100
> AS $BODY$
> --v_id INTEGER;
> v_title CHAR(10);
> v_amount NUMERIC;
> CURSOR book_cur IS
> SELECT title, amount FROM books2 WHERE id >=8;
> BEGIN
> OPEN book_cur;
> LOOP
> FETCH book_cur INTO v_title, v_amount;
> EXIT WHEN book_cur%NOTFOUND;
> INSERT INTO books2 (title, amount) VALUES (v_title, v_amount);
> END LOOP;
> COMMIT;
> CLOSE book_cur;
> END
> $BODY$;

"Normally, cursors are automatically closed at transaction commit."
https://www.postgresql.org/docs/devel/plpgsql-transactions.html

So the explicit CLOSE is not needed, if you have the COMMIT.

But then why have the COMMIT? The transaction will commit by default,
so I would remove that.

--
Simon Riggs http://www.EnterpriseDB.com/

Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

From

hubert depesz lubaczewski

Date:

15 December 2021, 14:36:44

On Fri, Dec 10, 2021 at 03:40:41PM +0000, James Kitambara wrote:
> There is no COMMIT in the loop for processing cursor data.
> Sorry I forget to share the procedure on my first email:
> Here is a procedure:
> -------------------------------------------------------
> CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2(
> )
> LANGUAGE 'edbspl'
>     SECURITY DEFINER VOLATILE PARALLEL UNSAFE 
>     COST 100
> AS $BODY$
>     --v_id         INTEGER;
>     v_title      CHAR(10);
> v_amount  NUMERIC;
>     CURSOR book_cur IS
>         SELECT title, amount FROM books2 WHERE id >=8;
> BEGIN
>     OPEN book_cur;
>     LOOP
>         FETCH book_cur INTO v_title, v_amount;
>         EXIT WHEN book_cur%NOTFOUND;
> INSERT INTO books2 (title, amount) VALUES (v_title, v_amount);
>     END LOOP;
> COMMIT;
>     CLOSE book_cur;
> END
> $BODY$;

Hi,
others helped you with the reported problem, but I'd like to point out
that your procedure is doing a job, and it's doing it poorly (slowly).

There is no need for iteration. There is no need for cursor. There is
even no need for procedure, but let's keep it there.

Your whole procedure can be simplified to:

CREATE OR REPLACE PROCEDURE public.temp_insert_in_books2()
LANGUAGE 'plpgsql'
    SECURITY DEFINER VOLATILE PARALLEL UNSAFE 
    COST 100
AS $BODY$
DECLARE
BEGIN
    INSERT INTO books2 (title, amount)
        SELECT title, amount FROM books2 where id >= 8;
END
$BODY$;

And that's it.

It will be faster (single insert, instead of one-for-each-row), and
definitely easier to read and maintain.

Best regards,

depesz