Marko Tiikkaja <marko@joh.to> wrote:
> Well, just off the top of my head a normal function invocation could be:
> one worker working on a single "order" started by a single end user to
> transfer money from one account to another. And we have *a lot* of code
> like this where there isn't a way to write the code in "set-oriented
> style" without inventing a time machine. Which just might be out of the
> scope of plpgsql2 (or perhaps as a GUC).
That's almost exactly a situation I was going to use to illustrate
where I *did* want set-oriented behavior. One hard rule in the
shop in question was that an application is *never* allowed to
leave a database transaction pending while waiting for user input.
Another was that a financial transaction must be committed to the
database as one database transaction, with ID numbers that were
assigned in commit sequence, with no gaps, to satisfy the auditors.
Just to complete the scope of the issue, each time a database
transaction was run, it got a random connection from a connection
pool, so temporary tables could not be used across transactions.
Think about that for a minute.
What we did was to create permanent work tables with a temporary ID
for a financial transaction in process as part of the primary key.
(The rest of the primary key matched the corresponding "normal"
table.) The user takes however much time it takes to populate the
financial transaction, which typically affects many tables
including at least two (and sometimes hundreds of) rows in the
TransactionDetail table. If they cancel out of the entry process
we delete all of the affected rows with one DELETE statement per
table. If they OK the financial transaction we copy the data from
the work tables to the normal tables with one INSERT and one DELETE
per table -- all in one database transaction. (The auditor
requirements were satisfied by some explicit locking and SEQUENCE
objects in that final database transaction.) Fast, clean, and
effective.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company