Once per quarter, we need to load a lot of data, which causes many updates across the database. We have an online transaction processing-style application, which we really want to stay up during the update job.
The programmer coded a stored procedure which does the job well … logically. But as a single PL/pgSQL stored procedure, it is one long-running transaction. At least, that is my interpretation of http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html#CO.PLPGSQL-PORTING-COMMIT – and in fact, we do get errors when we try little BEGIN-COMMIT blocks inside a stored procedure.
A single long-running transaction would be bad in production. A long run time = OK, but long-running transaction = site outage.
So I’m asking for advice on whether I can break this into small transactions without too much of a rewrite. Roughly, the algorithm is:
(1) One job dumps the data from the external source into a load table.
(2) Another job calls the stored procedure, which uses a cursor to traverse the load table. A loop for each record:
a. Processes a lot of special cases, with inserts and/or updates to many tables.
Unless this can be done within PL/pgSQL, I will have the programmer refactor job (2) so that the loop is in a java program, and the “normalization” logic in (a) – the guts of the loop – remain in a smaller stored procedure. The java loop will call that stored procedure once per row of the load table, each call in a separate transaction. That would both preserve the bulk of the PL/pgSQL code and keep the normalization logic close to the data. So the runtime will be reasonable, probably somewhat longer than his single monolithic stored procedure, but the transactions will be short.
We don’t need anything like SERIALIZATION transaction isolation of the online system from the entire load job.
Thanks for any ideas,
David Crane
DonorsChoose.org
