[PL/pgSQL] Commit every N rows. Is it possible ?
Hello,
I'm still trying to convert my PL/SQL stored procedures into PL/pgSQL. Now,
I have problem with commiting transaction every N rows:
**loop
fetch csr_ac into row_id;
if not FOUND then
exit;
end if;
counter := counter + 1;
delete from spm_audit where adt_id=row_id;
delete from spm_audit_pipeline_data where apd_adt_id=row_id;
global_counter := global_counter + 1;
if counter = rows_between_commit then
counter := 0;
commit;
end if;
end loop;
I'm digging into postgresql documentation but maybe I'm just not smart
enough to understand the way which transactions are being processed into
pgSQL. Is it possible to port above code to PL/pgSQL ?
Kind Regards.
--
-- audi vide sile --
Jeremiasz Miedzinski wrote:
I'm digging into postgresql documentation but maybe I'm just not smart
enough to understand the way which transactions are being processed into
pgSQL. Is it possible to port above code to PL/pgSQL ?
All functions, including pl/pgsql functions take place within a
transaction. You cannot commit, but you can catch errors and roll back
to savepoints within a transaction.
If you want to have multiple transactions you will need to step outside
of the database.
It's not clear to me why your function does what it does anyway. I can't
see why you wouldn't just do this as standard queries.
--
Richard Huxton
Archonet Ltd
2006/11/9, Richard Huxton <dev@archonet.com>:
It's not clear to me why your function does what it does anyway. I can't
see why you wouldn't just do this as standard queries.
As it was mentioned on http://orafaq.com/faqplsql.htm
Contrary to popular believe, one should *COMMIT less frequently* within a
PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the
frequency of commit, the sooner the extents in the rollback segments will be
cleared for new transactions, causing ORA-1555 errors.
So, I understand that if function/procedure in postgreSQL is treated as one
transaction I can for example execute 15000 delete queries and nothing
similar to ORA-1555 shouldn't happen.
Kind Regards.
--
-- audi vide sile --
Jeremiasz Miedzinski wrote:
2006/11/9, Richard Huxton <dev@archonet.com>:
It's not clear to me why your function does what it does anyway. I can't
see why you wouldn't just do this as standard queries.As it was mentioned on http://orafaq.com/faqplsql.htm
Contrary to popular believe, one should *COMMIT less frequently* within a
PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the
frequency of commit, the sooner the extents in the rollback segments
will be
cleared for new transactions, causing ORA-1555 errors.So, I understand that if function/procedure in postgreSQL is treated as one
transaction I can for example execute 15000 delete queries and nothing
similar to ORA-1555 shouldn't happen.
I don't believe we have ORA-1555 errors in PG. We don't have the
resources to implement all of Oracle's failure modes :-)
Two areas where you might want to keep an eye on resource usage though:
1. Lots of savepoints (exception handling in plpgsql)
2. Returning large result sets (where the function will assemble the
entire set before returning it). Consider returning a cursor if you want
millions of rows.
--
Richard Huxton
Archonet Ltd