Re: "stored procedures"

Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Apr 21, 2011 at 1:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.

> Does this mean you do or don't expect plpgsql to be able to run as
> procedure?  Should SPI based routines generally be able to run as a
> procedure (I hope so)?  If so, what API enhancements would be needed?
> (I was thinking, SPI_is_proc, or something like that).  I'd like to
> see plpgsql work as much as possible as it does now, except obviously
> you can't have exception handlers.

You can't have arithmetic, comparisons, or much of anything outside a
transaction with plpgsql.  That model just plain doesn't work for this
purpose, I think.  You really want a control language that's independent
of the SQL engine, and for better or worse plpgsql is built inside that
engine.

> What about cancelling? Cancel the current running query, or the whole
> procedure (I'm assuming the latter?  How would that work?

Good question.  If you're imagining that the SP could decide to cancel a
database request partway through, it seems even further afield from what
could reasonably be done in a single-threaded backend.

Maybe we should think about the SP controlling a second backend (or even
multiple backends?) that's executing the "transactional" operations.
dblink on steroids, as it were.
        regards, tom lane