Re: PL/PgSQL discussion

Hannu Krosing wrote:
>
> jwieck(at)debis(dot)com (Jan Wieck) wrote:
>
> > Someone gave a hint about global variables existing during a
> > session. What is a session than? One transaction? The
> > backends lifetime?
>
> 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?

> >
> > PL/pgSQL is a block oriented language. A block is defined as
> >
> > [<<label>>]
> > [DECLARE
> > -- declarations]
> > BEGIN
> > -- statements
> > END;
>
>
>
> >
> >
> > There can be any number of subblocks in the statements
> > section of a block. Subblocks can be used to hide variables
> > from outside a block of statements (see Scope and visability
> > below).
>
> 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?

>
> >
> >
> > Comments
> >
> > There are two types of comments in PL/pgSQL. A double dash
> > '--' starts a comment that extends to the end of the line. A
> > '/*' starts a block comment that extends to the next '*/'.
> > Block comments cannot be nested, but double dash comments can
> > be enclosed into a block comment.
>
> And vice versa : block comment delimiters can be commented out by --

Right - works already :-)

>
> >
> >
> > Declarations
> >
> > All variables, rows and records used in a block or it's
> > subblocks must be declared in the declarations section of the
> > block. The parameters given to the function are
> > automatically declared with the usual identifiers $n.
>
> 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.

If we sometimes implement a new function call interface, this
might be possible. Some details about what I have in mind:

Add a field to pg_proc that tells the backend the call
interface the function uses.

Create a new catalog pg_parameter, that holds the
parameter names and other information (like notnull,
atttypmod etc.). So a function can be defined to expect a
VARCHAR(20) NOT NULL.

The new call interface then hands out more information to
the function than now. It's the functions Oid, the
parameter Datums, a character array telling which of the
Datums are NULL and the usual bool pointer where the
function can tell that it's return value is NULL.

> > Returning from the function
> >
> > RETURN <expr>;
> >
> > The function terminates and the value of <expr> will be
> > returned to the upper executor. The return value of a
> > function cannot be undefined. If control reaches the end
> > of the toplevel block of the function without hitting a
> > RETURN statement, a runtime error will occur.
>
> 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.

> > A conditional loop that is executed as long as the
> > evaluation of <expr> returns true.
> >
> > [<<label>>]
> > FOR <name> IN [REVERSE] <expr>..<expr> LOOP
> > -- statements
>
> > END LOOP.
>
> Perhaps PL/PgSQL should have some notation for specifying immediate lists of other kinds
> of values as well,perhaps like ['aa','bb','cc'], so that one cold loop over not only
> integers. Perhaps even with type specifiers, like [::time '22.01', '13.47.05', '15.20']

Good issue. Could be done.

>
> Keep up the nice work!
>
> Hannu
>
>

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

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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #