Re: The transaction that "happens" with function - Mailing list pgsql-general

--On Tuesday, June 10, 2003 9:51 AM -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> jr@amanue.com (Jim Rosenberg) writes:
>> I need some clarification on the issue of functions and transactions.
>
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=tutorial-
> transactions.html See particularly the last couple of paragraphs.

Thanks!

Oh oh oh oh [Sound of light bulbs going on ...] I see where i went astray.
Some databases have the concept -- missing from PostgreSQL -- of *no
transaction*. In PostgreSQL, it appears, whenever I'm "in" SQL I'm in
*some* transaction. In some databases it's not necessary to have "any"
transaction if one hasn't changed anything, but PostgreSQL doesn't work
this way.

I don't suppose "no transaction" is easier to do than nested transactions
...

This reminds me of my favorite joke [an original creation, I might add :-)]
about why it's so hard to get no-salt potato chips at the grocery store: it
seems there is a world-wide cartel that controls the price of no-salt.

--On Tuesday, June 10, 2003 2:33 PM +0100 Richard Huxton <dev@archonet.com>
wrote:

>> Take a payroll system. Each person's
>> pay may be a transaction at the database level, but you don't want to
>> allow a person not to be paid "by mistake". It would be horrendous to
>> make the whole pay a single transaction. Do you really want to roll back
>> an entire payroll because of a problem with one person's check? I don't
>> think so.
>
> Hmm - I'd disagree on this specific example. At some point you do want a
> "commit all changes" transaction to mark the payroll finished.

The thinking here is very simple. There are twenty very burly guys outside
your office waiting for their paychecks. There is one very mild-mannered
sweet director of graphics design who is not waiting outside your office.
She is in Hawaii, on vacation. She has already agreed to be the company
guinea pig for the new deduction scheme you just launched. You already
warned her that this might mess up her pay check, we'll have to make an
adjustment. That's OK, she says, I won't be here. And sure enough, her pay
blows up. And you're going to roll back the WHOLE PAYROLL because of this?
As I said, I don't think so.

A structure where there's a series of business transactions that are posted
together, but where at the database level each business transaction is a
*separate* database transaction is *very common*. One needs to ask: *WHY*
are transactions rolled back, anyway. The answer usually is that there's
some inconsistency in the data, and it's always easier to deal with such
things sooner rather than later. You roll back the transaction because it's
*dangerous* to let the data go forward. But it may not be dangerous for
there to be more data yet to be posted. In this situation, you post what is
postable, fail what is not. But you still may want the whole posting
routine encapsulated as a unit of business logic where users can't temper
with it.

I could imagine making the whole payroll be a single transaction if there
were some rule at the company that *says* explicitly that no one can be
paid unless everyone is paid.

* * *

At any rate, such issues are a function of the individual application, and
shouldn't be dictated by the database. I repeat that there are very strong
business reasons for insisting that business logic be encapsulated at a
higher-level unit than the database-level transaction.