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

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.

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. E.g. I want a query loop, and
I want the transaction scoped to the contents of each loop iteration,
not the whole function. So: since I can't have nested transactions [yet!],
here's what I do: the function begins by executing COMMIT. Now there's
no transaction in force. I start my query loop, and inside the body of the
loop I put BEGIN and COMMIT / ROLLBACK just like I would if the statements
weren't inside a function. I make sure before exiting to have a dummy
BEGIN. Arguably, this is a demented way to program, but *will it work*?
[I gather it won't ...] If it won't work then that means there *is*
something "special" about the function transaction, and I would like to
see this documented.

What about error handling / ROLLBACK? How exactly does this happen?
Suppose my function is written in -- let's say -- pgTCL. I'm using
catch to trap errors. Normally I would expect a catch handler to do a
ROLLBACK if the code assumes a transaction is happening. Do I do
that inside a function in the usual way? Or does something "magic"
happen to roll back this transaction? If there is magic, how is a catch
handler supposed to "register" an error?

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.

* * *

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 ...) 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. 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.

PostgreSQL took a good step when it allowed a function to be invoked
under the privilege of the function creator. Encapsulating business
logic is *all about* privilege escalation. Obviously, nested transactions
will be a *HUGE* step forward. [I can't wait ...] But even with nested
transactions, we will still need clear documentation on how any transaction
wrapping a function works.