BUG #18531: err when using 'current of' with incremental COMMIT

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: dzja112(at)gmail(dot)com
Subject: BUG #18531: err when using 'current of' with incremental COMMIT
Date: 2024-07-09 11:00:29
Message-ID: 18531-c6dddd33b8555fd2@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18531
Logged by: Dzmitry Jachnik
Email address: dzja112(at)gmail(dot)com
PostgreSQL version: 14.2
Operating system: x86_64 GNU/Linux
Description:

I tried using 'current of' syntax (like doc. 43.7.3.3.)
and had have ERROR: cursor "bulk_collection_cur" is held from a previous
transaction

If using regular ` WHERE id = l_rec.id; ` I hadn't any error

EXAMPLE:
```

CREATE OR REPLACE PROCEDURE crm_import.etlp_deals(IN i_id bigint DEFAULT
NULL::bigint, IN i_limit integer DEFAULT null)
LANGUAGE plpgsql
AS $procedure$
DECLARE

result jsonb;
COUNTER int := 0;

bulk_collection_cur CURSOR FOR
SELECT *
FROM crm_import.deals dew
WHERE COALESCE(dew.etl_stage,'NOT_LOADED') not IN ('LOADED',
'IGNORE')
-- AND dew.id = any(l_id)
AND (dew.id = i_id OR i_id IS null)
ORDER BY dew.load_date, dew.dt
FOR UPDATE ;
begin

<<COLLECTION>>
BEGIN
FOR l_rec IN bulk_collection_cur
LOOP
COUNTER := COUNTER + 1;
RESULT := crm_import.etl_deals(row_to_json(l_rec.*)::jsonb);

UPDATE crm_import.deals dsp
SET etl_protocol = RESULT
-------------------
--v.1 SQL Error [24000]: ERROR: cursor "bulk_collection_cur" is
held from a previous transaction
WHERE CURRENT OF bulk_collection_cur;
--v.2 WITHOUT ERROR
--WHERE id = l_rec.id;
-------------------
RAISE NOTICE 'counter= %', counter;
IF mod(counter, i_limit) = 0 THEN
RAISE NOTICE 'COMMIT';
COMMIT;
END IF;
END LOOP;
END COLLECTION;

end $procedure$
;
```
That combine 'current of' and 'commit' at one loop is bad idea?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Dunstan 2024-07-09 16:01:17 Re: pg_rewind fails on Windows where tablespaces are used
Previous Message Amit Kapila 2024-07-09 09:27:30 Re: Potential data loss due to race condition during logical replication slot creation