Обсуждение: ERROR ON INSERTING USING A CURSOR IN EDB POSTGRESQL

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/

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