Re: "stored procedures"

On Thu, Apr 21, 2011 at 5:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

I'm arguing against a separate language, or at least questioning if
plpgsql truly can't be run without an outer transaction context. Just
because a transaction isn't set up on procedure invocation, doesn't
mean you can't set them up to do things in the procedure?

It wouldn't bother me in the lest that if in plpgsql procedures if you
had to set up and tear down a transaction on every line. You can
always dip into a function if/when you need the turbo boost. plpgsql
is kind of a special case anyways in that it uses sql engine for a lot
of core operations. The other pls use their own engines to manage non
query code.

Setting up a new control language implies that postgres needs to know
the procedure language textually so it can read off a line and do
something with it. I don't like this restriction -- wouldn't it be
better if the current crop of language handlers could run procedures
without major changes? C functions with SPI? However it's internally
implemented, the more userland mindspace recovered for use of writing
procedures the better off we are.

merlin