agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
6+ messages / 4 participants
[nested] [flat]
* ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-09 10:36 James Kitambara <jameskitambara@yahoo.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: James Kitambara @ 2021-12-09 10:36 UTC (permalink / raw)
To: pgsql-sql; +Cc: James Kitambara <jameskitambara@yahoo.co.uk>
ISSUE OF CURSOR ON THE EDB POSTGRESQL
I have the table books2 below with those fields on EDBPostgreSQL.
CREATE TABLE IF NOT EXISTSpublic.books2
(
id integer NOT NULL DEFAULTnextval('books2_id_seq'::regclass),
title character(10) COLLATEpg_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 IDis 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() line15 at CLOSE
SQL state: 34000
HOW CAN I REMOVE THATERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ONTHE TABLE USING CURSORS.
PLEASE CAN ANYONE ASSIST.
Table Data after running the procedure is described below:
Attachments:
[image/png] 1639045991057blob.jpg (23.8K, ../../820139578.307641.1639046195013@mail.yahoo.com/3-1639045991057blob.jpg)
download | view image
[image/png] 1639046039477blob.jpg (31.8K, ../../820139578.307641.1639046195013@mail.yahoo.com/4-1639046039477blob.jpg)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-09 10:55 Sandeep Saxena <sandeep.lko@gmail.com>
parent: James Kitambara <jameskitambara@yahoo.co.uk>
0 siblings, 1 reply; 6+ messages in thread
From: Sandeep Saxena @ 2021-12-09 10:55 UTC (permalink / raw)
To: James Kitambara <jameskitambara@yahoo.co.uk>; +Cc: pgsql-sql
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
>
> [image: Inline image]
>
>
>
> 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:
>
> [image: Inline image]
>
>
>
Attachments:
[image/jpeg] 1639046039477blob.jpg (31.8K, ../../CAA3fAREzZF1J5hoBO+YmmVcvTe7Jdd9O3S9Mrk=TzTFXew_dHw@mail.gmail.com/3-1639046039477blob.jpg)
download | view image
[image/jpeg] 1639045991057blob.jpg (23.8K, ../../CAA3fAREzZF1J5hoBO+YmmVcvTe7Jdd9O3S9Mrk=TzTFXew_dHw@mail.gmail.com/4-1639045991057blob.jpg)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-10 15:40 James Kitambara <jameskitambara@yahoo.co.uk>
parent: Sandeep Saxena <sandeep.lko@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: James Kitambara @ 2021-12-10 15:40 UTC (permalink / raw)
To: Sandeep Saxena <sandeep.lko@gmail.com>; +Cc: pgsql-sql
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 100AS $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 EDBPostgreSQL.
CREATE TABLE IF NOT EXISTSpublic.books2
(
id integer NOT NULL DEFAULTnextval('books2_id_seq'::regclass),
title character(10) COLLATEpg_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 IDis 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() line15 at CLOSE
SQL state: 34000
HOW CAN I REMOVE THATERROR?. ALSO NOTE THAT I ALWAYS GET THIS ERROR WHEN UPDATING OR INSERTING DATA ONTHE TABLE USING CURSORS.
PLEASE CAN ANYONE ASSIST.
Table Data after running the procedure is described below:
Attachments:
[image/jpeg] 1639046039477blob.jpg (31.8K, ../../615924257.1194226.1639150841188@mail.yahoo.com/3-1639046039477blob.jpg)
download | view image
[image/jpeg] 1639045991057blob.jpg (23.8K, ../../615924257.1194226.1639150841188@mail.yahoo.com/4-1639045991057blob.jpg)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-14 10:58 Simon Riggs <simon.riggs@enterprisedb.com>
parent: James Kitambara <jameskitambara@yahoo.co.uk>
1 sibling, 1 reply; 6+ messages in thread
From: Simon Riggs @ 2021-12-14 10:58 UTC (permalink / raw)
To: James Kitambara <jameskitambara@yahoo.co.uk>; +Cc: Sandeep Saxena <sandeep.lko@gmail.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-14 15:22 James Kitambara <jameskitambara@yahoo.co.uk>
parent: Simon Riggs <simon.riggs@enterprisedb.com>
0 siblings, 0 replies; 6+ messages in thread
From: James Kitambara @ 2021-12-14 15:22 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: Sandeep Saxena <sandeep.lko@gmail.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL
@ 2021-12-15 11:36 hubert depesz lubaczewski <depesz@depesz.com>
parent: James Kitambara <jameskitambara@yahoo.co.uk>
1 sibling, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2021-12-15 11:36 UTC (permalink / raw)
To: James Kitambara <jameskitambara@yahoo.co.uk>; +Cc: Sandeep Saxena <sandeep.lko@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-12-15 11:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-09 10:36 ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL James Kitambara <jameskitambara@yahoo.co.uk>
2021-12-09 10:55 ` Sandeep Saxena <sandeep.lko@gmail.com>
2021-12-10 15:40 ` James Kitambara <jameskitambara@yahoo.co.uk>
2021-12-14 10:58 ` Simon Riggs <simon.riggs@enterprisedb.com>
2021-12-14 15:22 ` James Kitambara <jameskitambara@yahoo.co.uk>
2021-12-15 11:36 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox