BUG #18531: err when using 'current of' with incremental COMMIT
От | PG Bug reporting form |
---|---|
Тема | BUG #18531: err when using 'current of' with incremental COMMIT |
Дата | |
Msg-id | 18531-c6dddd33b8555fd2@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #18531: err when using 'current of' with incremental COMMIT
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 18531 Logged by: Dzmitry Jachnik Email address: dzja112@gmail.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?
В списке pgsql-bugs по дате отправления: