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

On Tue, 10 Jun 2003, (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.
>
> 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.

Not exactly anything special.

What it is is that each statement gets an implicit transaction around it not
that each function starts a transaction.

Your SELECT * FROM your_table has an implicit transaction, if there isn't
already one started, as does UPDATE your_table SET your_field = your_value. So
when you invoke your function it's not because you are invoking a function that
means that a transaction is started. In your function you've been given the
opportunity to perform extra processing in a select query and so you can get
the opportunity to 'see' that you're in a transaction whereas normally you'd
just be sat twiddling your thumbs waiting for psql to spew the results out at
you.


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

Not sure, I'm sure it must be somewhere but I don't know off hand.

Nested transactions of course let you do what you want, probably still a good
idea not to try and commit the outermost statement's transaction though.


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

Why not? I can sort of see your logic but if you're doing it within a database
function then that function also has to take care of making the payments
doesn't it?


--
Nigel Andrews