Re: [HACKERS] Re: PL/PgSQL discussion

jwieck(at)debis(dot)com (Jan Wieck) wrote:
> Hannu Krosing wrote:
> > jwieck(at)debis(dot)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(at)illustra(dot)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.