Re: "stored procedures"

2011/4/21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Josh Berkus <josh(at)agliodbs(dot)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:

It doesn't mean so SQL are inside SP non transactional. Stored
Procedure is just client module moved on server. You can call SQL
statements from psql without outer implicit or explicit transaction
too.

It mean - a CALL statement should not start a outer transaction when
it isn't requested, but all inner SQL statements runs in own
transactions.

The questions about mutable or immutable parameters are important -
but it doesn't mean so SP without outer transactions are impossible.

Regards

Pavel

>
> 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 that we could finesse #1 and #2, along these lines:
> The CALL command is ordinary SQL but not allowed inside a transaction
> block, much like some existing commands like VACUUM.  So we start a
> transaction to parse and execute it.  The CALL looks up the procedure
> definition and evaluates any input arguments.  It then copies this info to
> some outside-the-transaction memory context, terminates its transaction,
> and calls the procedure.  On return it starts a new transaction, in
> which it can call the output functions that are going to have to be
> executed in order to pass anything back to the client.  (This implies
> that OUT argument values are collected up during SP execution and not
> actually passed back to the client till later.  People who were hoping
> to stream vast amounts of data to the client will not be happy.  But
> I see no way around that unless you want to try to execute output
> functions outside a transaction, which strikes me as a quagmire.)
>
> I'm less sure what to do about #3.  The most attractive approach would
> probably be to make people use a non-SQL script interpreter --- perl,
> python, or whatever floats your boat --- which would likely mean that
> we have not just one SP implementation language but N of them.  But
> we've solved that problem before.
>
> Calling another SP ... particularly one with a different implementation
> language ... could be a bit tricky too.  The above proposal assumes that
> SPs are always entered outside a transaction, but do we want to make
> that same restriction for the call-another-SP case?  And if not, how's
> it going to work?  Again, you'll have to be inside a transaction at
> least long enough to get the SP's definition out of the catalogs.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>