Re: [HACKERS] Re: PL/PgSQL discussion

jwieck@debis.com (Jan Wieck) wrote:
> Hannu Krosing wrote:
> > jwieck@debis.com (Jan Wieck) wrote:
...
> > I think it is a time from connect to disconnect, which currently equals to backends
> > lifetime, but may in future be shorter, if we will implement a backend pool for
> > quick-starting servers.
>
>     Hmmm  -  how  does  a language handler then notice that a new
>     session began?

Either when the backend exits, or is re-initted to go back to the idle pool.


> > >         PL/pgSQL is a block oriented language. A block is defined as
> > >             [<<label>>]
> > >             [DECLARE
> > >                 -- declarations]
> > >             BEGIN
> > >                 -- statements
> > >             END;
...
> > I think that subblock should also be used as the extent of IF, FOR ... LOOP, WHILE and
> > other such statements.
> > Then we would not need the END IF, END LOOP etc.
>
>     The LOOP ... END LOOP etc. syntax  is  just  what  I  saw  in
>     Oracles   PL/SQL   documentation.  I  could  also  live  with
>     BEGIN...END, but what is it good for to be different?

I am not convinced that imitating Oracle is necessarily the best possible way
to proceed. If there is a standard, and I think there is at least a proposal
(called PSM, if memory serves), we might (but only might) want to do that.
Otherwise, either lets steal something worth stealing or invent something
better than whats out there.


> > How hard would it bet to have named parameters, or why must we use alias?
>
>     That isn't subject to the PL  handler.  All  the  PL  handler
>     knows about the function is in pg_proc and pg_type. There are
>     no parameter names, and that's what the ALIAS idea came from.
...
> > >         Returning from the function
> > >
> > >                 RETURN <expr>;
> > >
> > What can <expr> be?
> >
> > Possibilities: null, single value, record, recordset
> >
> > AFAIK, recordsets are usually returned by more strange constructs, like haveing some
> > kinds of breakpoints inside the loop that either returns a record or some EOF token.
> >
>     Currently  only  'null'  and  'single  value'.  The  executor
>     doesn't  accept anything else for non-sql language functions.
>     PL functions are treated by the executor like 'C'  functions.

This limitation suggests that PL/pgSQL functions want to _be_ SQL functions,
not 'C' functions. Handy to be able to write:

begin
    if $1 = "totals" then
       select sum(qty), sum(qty) * price from sales_orders where ...;
    else if $1 = "details"
       select qty, price from sales_orders where ...

Ok, lousy example, but I have seen this kind of thing in apps ...


>     Already  reached  the  point  of  no  return.  The first tiny
>     function ran without problems:

I am impressed.

>         CREATE FUNCTION f1(int4, int4) RETURNS int4 AS '
>             BEGIN
>                 RETURN $1 + $2;
>             END;
>         ' LANGUAGE 'plpgsql';
>
>     I set up a little test table with 2  int4  fields  containing
>     some  thousand  records. Then I wrote equivalent functions in
>     'sql', 'plpgsql' and 'pltcl'.   The  execution  times  for  a
>     query
>
>         SELECT sum(func(a, b)) FROM t1;
>
>     are:
>
>         Builtin SQL language    100%
>         PL/Tcl                  180%
>         PL/pgSQL                230%
>
>     PL/Tcl is slower than builtin SQL because the internals of it
>     require the two parameters to be converted to their  external
>     representation,  than  calling the Tcl interpreter who parses
>     them back to numbers, calculates the result,  returns  it  as
>     string and then it's parsed back to int4 internal value.
>
>     In  the PL/pgSQL case I haven't expected that big performance
>     loss.  The calculation is internally done with a saved  query
>     plan (made on the first call) that does a
>
>         SELECT $1 + $2
>
>     with  two  int4  parameters.  This  is  exactly  what the SQL
>     version of the above does!  And >95% of  the  execution  time
>     for  the  function  call  are  spent  in  SPI_execp().  Since
>     SPI_execp()  calls  ExecutorRun()  directly,  I   think   the
>     querydesc  creation and/or plan copying on each invocation is
>     the time  consuming  part.  I  assume  that  there  are  some
>     optimizable corners in SPI where we can gain more speed. So I
>     continue with PL/pgSQL as it is now and speed it up later  by
>     tuning SPI.

I think you might want to do some profiling to find where the time is
really going. Not to throw water on your parade, but being slower than
tcl is something of an achievement ;-).

I wish I could be of more use than this, but that will have to wait
until I get some time to look over the postgres code a bit. It is similar
to Illustra in many ways, but the two lines branched apart quite a while
ago and have gone in different directions especially in the language areas.


David Gould            dg@illustra.com           510.628.3783 or 510.305.9468
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - I realize now that irony has no place in business communications.