Re: SQL procedures

On 31 October 2017 at 17:23, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> I've been working on SQL procedures. (Some might call them "stored
> procedures", but I'm not aware of any procedures that are not stored, so
> that's not a term that I'm using here.)

Looks good

> Everything that follows is intended to align with the SQL standard, at
> least in spirit.

+1

> This first patch does a bunch of preparation work. It adds the
> CREATE/ALTER/DROP PROCEDURE commands and the CALL statement to call a
> procedure.

I guess it would be really useful to have a cut-down language to use
as an example, but its probably easier to just wait for PLpgSQL.

You mention PARALLEL SAFE is not used for procedures. Isn't it an
architectural restriction that procedures would not be able to execute
in parallel? (At least this year)

> It also adds ROUTINE syntax which can refer to a function or
> procedure.

I think we need an explanatory section of the docs, but there doesn't
seem to be one for Functions, so there is no place to add some text
that says the above.

I found it confusing that ALTER and DROP ROUTINE exists but not CREATE
ROUTINE. At very least we should say somewhere "there is no CREATE
ROUTINE", so its absence is clearly intentional. I did wonder whether
we should have it as well, but its just one less thing to review, so
good.

Was surprised that pg_dump didn't use DROP ROUTINE, when appropriate.

> I have extended that to include aggregates. And then there
> is a bunch of leg work, such as psql and pg_dump support. The
> documentation is a lot of copy-and-paste right now; that can be
> revisited sometime. The provided procedural languages (an ever more
> confusing term) each needed a small touch-up to handle pg_proc entries
> with prorettype == 0.
>
> Right now, there is no support for returning values from procedures via
> OUT parameters. That will need some definitional pondering; and see
> also below for a possible alternative.
>
> With this, you can write procedures that are somewhat compatible with
> DB2, MySQL, and to a lesser extent Oracle.
>
> Separately, I will send patches that implement (the beginnings of) two
> separate features on top of this:
>
> - Transaction control in procedure bodies
>
> - Returning multiple result sets

Both of those would be good, though my suggested priority would be
transaction control first and then multiple result sets, if we cannot
have both this release.

> (In various previous discussions on "real stored procedures" or
> something like that, most people seemed to have one of these two
> features in mind. I think that depends on what other SQL systems one
> has worked with previously.)

Almost all of the meat happens in later patches, so no other review comments.

That seems seems strange in a patch of this size, but its true.
Procedures are just a new type of object with very little interaction
with replication, persistence or optimization.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services