Re: "stored procedures"

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