On 2014-09-02 19:33, Kevin Grittner wrote:
Marko Tiikkaja <ma...@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.
Of course the transaction isn't open while waiting for user input. But
for the lifetime of the "order", almost all of the code is nearly always
dealing with one of a number of things, and almost never with more than
one of anything.
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.
Sounds like in this case you'd only use set-oriented programming at the
end of the transaction, no? Or was there a separate application
gathering the details about the transaction, which then just sent all of
that information in a batch into the database?
.marko
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
