Re: PL/pgSQL 2

On 2014-09-02 19:33, Kevin Grittner wrote:
> Marko Tiikkaja <marko(at)joh(dot)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