Re: "stored procedures"

On Mon, Apr 25, 2011 at 9:18 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> Procedures unlike functions however can no longer rely that
>> catalogs remain static visibility wise through execution for
>> functions.
>
> If you start from the perspective that stored procedures are in many
> respects more like psql scripts than functions, this shouldn't be
> too surprising.  If you have a psql script with multiple database
> transactions, you know that other processes can change things
> between transactions.  Same deal with SPs.
>
> The whole raison d'être for SPs is that there are cases where people
> need something *different* from functions.  While it would be *nice*
> to leverage plpgsql syntax for a stored procedure language, if it
> means we have to behave like a function, it's not worth it.

As noted above it would be really nice if the SPI interface could be
recovered for use in writing procedures. plpgsql the language is less
of a sure thing, but it would be truly unfortunate if it couldn't be
saved on grounds of user-retraining alone. If a sneaky injection of
transaction manipulation gets the job done without rewriting the
entire then great, but it's an open question if that's possible, and
I'm about 2 orders of magnitude unfamiliar with the code to say either
way. I'm inclined to just poke around and see what breaks.

OTOH, if you go the fully textual route you can get away with doing
things that are not at all sensible in the plpgsql world (or at least
not without a serious rethink of how it works), like connecting to
databases mid-procedure, a cleaner attack at things like running
'CLUSTER', than the flush transaction state methodology above.

So I see we have three choices:
1. recover SPI, recover plpgsql (and other pls), transaction flush
command (SPI_flush()?)
2. recover SPI, replace plpgsql (with what?)
3. no spi, custom built language, most flexibility, database
reconnects, aka, 'tabula rasa'

#1 is probably the easiest and most appealing on a lot of levels, but
fraught with technical danger, and the most limiting?

merlin