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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
