Re: The transaction that "happens" with function invocation

On Tuesday 10 Jun 2003 2:03 pm, Jim Rosenberg wrote:
> I need some clarification on the issue of functions and transactions.
> I gather -- from bits and pieces of various mailing list postings --
> that whenever a function created with CREATE FUNCTION is invoked,
> there is some kind of "automatic" transaction wrapping this function
> invocation. While I would rate the quality of PostgreSQL documentation
> overall to be *EXCELLENT*, on this one issue there are some gaps, it
> seems to me. We need complete documentation on just exactly how this
> "automatic function transaction" works. There are several issues.

It takes place inside the same transaction as anything else. There is no
additional transaction "wrapping" a function-call. Any SQL statement occurs
within a transaction, either explicitly (BEGIN...COMMIT) or implicitly
(autocommit).

> Is this an "ordinary" transaction, or is it "special"? If it is special,
> in exactly what ways? If it's ordinary, then the following should work,
> even absent nested transactions. Suppose I want to create a function in
> a loadable language where I make my own decision about where the
> transaction boundaries are going to be.

Sorry - can't do. This will have to wait until we have nested transactions.

> If these issues *are* discussed in the documentation, please accept
> my apology and let me know where, but if not please please please
> document these things.

Think you've got the wrong end of the stick somewhere. Is there a part of the
docs which implies this? (in which case they need some rewriting)

> I'm still trying to decide if PostgreSQL functions can "really" be
> used to encapsulate business logic, or if it requires a 3-tier
> architecture to do it properly. (I suspect the latter ...)

Yep - latter.

> Business
> logic is tricky stuff. I *hope* the designers of PostgreSQL are not
> making a decision for me that this encapsulation "has to" take place
> at exactly the granularity of the transaction.

The decision is which features to work on first. Nested transactions impact a
lot of other areas.

> That's a decision I
> want to make for myself. There are many cases in business logic where
> you want to have many transactions at the database level rolled into a
> single encapsulated unit of work. 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. Having said
that, nested transactions allowing a function to rollback sub-transactions
will be a big gain in many cases.

At present, you'll have to do it at the application level I'm afraid.

--
Richard Huxton