Re: "stored procedures"

On Thu, Apr 21, 2011 at 3:51 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Thu, Apr 21, 2011 at 2:37 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Thu, Apr 21, 2011 at 2:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>>> Josh Berkus <josh@agliodbs.com> wrote:
>>>>> ** question: if an SP is called by another SP, what is its
>>>>> transaction context?
>>>
>>>> Entering or leaving an SP should not start or end a transaction.
>>>
>>> That all sounds mighty hand-wavy and at serious risk of tripping over
>>> implementation details.  Some things to think about:
>>>
>>> 1. Are you expecting the procedure definition to be fetched from a
>>> system catalog?  You're going to need to be inside a transaction
>>> to do that.
>>>
>>> 2. Are you expecting the procedure to take any input parameters?
>>> You're going to need to be inside a transaction to evaluate the
>>> inputs, unless perhaps you restrict the feature to an extremely
>>> lobotomized subset of possible arguments (no user-defined types,
>>> no expressions, just for starters).
>>>
>>> 3. What sort of primitive operations do you expect the SP to be
>>> able to execute "outside a transaction"?  The plpgsql model where
>>> all the primitive operations are really SQL ain't gonna work.
>>
>> I think we could handle a lot of these details cleanly if we had
>> autonomous transactions as a system primitive.  When you enter a
>> stored procedure at the outermost level, you begin a transaction,
>> which will remain open until the outermost stored procedure exits.
>
> If you do it that (base it on AT) way, then you can't:
> 1) call any utility command (vacuum, etc)
> 2) run for an arbitrary amount of time
> 3) discard any locks (except advisory)
> 4) deal with serialization isolation/mvcc snapshot issues that plague functions.
>
> Points 2 & (especially) 4 for me are painful.
>
> #4 explained:
> If you are trying to tuck all the gory mvcc details into server side
> functions, there is no real effective way to prevent serialization
> errors because the snapshot is already made when you enter the
> function.  Even if you LOCK something on function line#1, it's already
> too late.  No transaction procedures don't have this problem and allow
> encapsulating all that nastiness in the server.

Yes, those sound like a potent set of restrictions that "gut" what the
facility ought to be able to be useful for.

If what you want is something that runs inside a pre-existing
transaction, that rules out doing VACUUM or, really, *anything* that
generates transactions, without jumping through hoops to try to change
their behaviour.

My preference would be to expect that stored procedures are sure to
generate at least one transaction, and potentially as many more as
they choose to generate.

One of the most recent things I implemented was a process that does
bulk updates to customer balances.  We don't want the balance tuples
locked, so the process needs to COMMIT after each update.

At present, that means I'm doing a round trip from client to server each time.

If I had these "autonomous transaction procedures," I could perhaps do
the whole thing in a stored procedure, which would:
a) Pull the list of transactions it's supposed to process;
b) Loop on them:  - BEGIN; Do the processing for a transaction, COMMIT.

That's not terribly different from a vacuum utility that:
a) Pulls a list of tables it's supposed to vacuum;
b) Loop on them:   VACUUM the table

Autovac ought to make that sort of thing limitedly useful; you'd
usually rather just use autovac.

Mind you, we might discover that implementing autovac mostly in the
stored procedure language is easier and better than having it mostly
in C.  And this might further make it easy to add "hooks" to allow
site-specific logic to affect autovacuum policy.

(Note that Slony-I version 1.0, 1.1, and possibly 1.2 had the 'cleanup
thread' which notably vacuums tables mostly written in C.  2.0 shifted
the bulk of the logic into pl/pgsql, which made it much simpler to
read and verify, and made some of the components usable by
administrators.)

I'd expect SP to NOT be nestable, or at least, not in a sense that
allows rolling back activity of a "child" that thought it COMMITed
work.

It seems to me that we've already got perfectly good stored functions
that are strictly inside an existing transactional context - if you
want logic that's doing that, then use a SF, that's already perfectly
good for that, and you should use that.  If you want a stored
procedure that runs its own transaction(s), do so; don't expect every
kind of transactional logic out of SPs.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"