Thread: [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

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