Re: PL/PgSQL discussion

Hannu Krosing wrote:

jwieck@debis.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@debis.com (Jan Wieck) #

Jan Wieck wrote:

Hannu Krosing wrote:

jwieck@debis.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?

Probably by defining the reset_session or init_session functions for the language handler and
calling it at the connect time.

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?

then again we don't need the block delimiters ;)

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.

I just meant them as an automatic way to declare and use aliases for $N, like for example C
does currently.

The calling function does not know the local names of called function in C either.

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.

This again can be done by automatically rewriting these to additional statements to check for
this as first things in the function body. It makes only a theoretical difference if the error
is reportid before the call or just after it.

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.

Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
scope of this tutorial', but not impossible ;)

---------

Hannu

Hannu Krosing wrote:

Jan Wieck wrote:

Hannu Krosing wrote:

jwieck@debis.com (Jan Wieck) wrote:

Hmmm - how does a language handler then notice that a new
session began?

Probably by defining the reset_session or init_session functions for the language handler and
calling it at the connect time.

Not in the current implementation of the backend/pl_handler
interface. But a global variable SessionId might help.

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?

then again we don't need the block delimiters ;)

Forgotten in the 1st draft. Variables are initialized to
their default values every time a block/subblock is entered.
Not only when the function is entered.

...
FOR i IN 1..10 LOOP
DECLARE
flag bool DEFAULT false;
n integer DEFAULT 0;
BEGIN
...
END;
END LOOP;
...

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.

This again can be done by automatically rewriting these to additional statements to check for
this as first things in the function body. It makes only a theoretical difference if the error
is reportid before the call or just after it.

But this rewriting must be done when the function is created.
At this time, the pl_handler and it's parser aren't called.
It is done by the backends main parser. For loadable
procedural languages, the main parser doesn't know anything
about the languages syntax or if the string given after AS is
a program text at all. It only creates the pg_proc tuple.

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.

Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
scope of this tutorial', but not impossible ;)

I tried that really hard with no luck. And I know
ExecMakeFunctionResult() pretty good. But I'll give it
another shot when PL/pgSQL reached a state where it makes
sense.

Jan

--

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

Jan Wieck wrote:

Hmmm - how does a language handler then notice that a new
session began?

Probably by defining the reset_session or init_session functions for the language handler and
calling it at the connect time.

Not in the current implementation of the backend/pl_handler
interface. But a global variable SessionId might help.

Neither do we have backend pooling now.

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?

then again we don't need the block delimiters ;)

Forgotten in the 1st draft. Variables are initialized to
their default values every time a block/subblock is entered.
Not only when the function is entered.

...
FOR i IN 1..10 LOOP
DECLARE
flag bool DEFAULT false;
n integer DEFAULT 0;
BEGIN
...
END;
END LOOP;
...

Ok, this makes sense.

This again can be done by automatically rewriting these to additional statements to check for
this as first things in the function body. It makes only a theoretical difference if the error
is reportid before the call or just after it.

But this rewriting must be done when the function is created.
At this time, the pl_handler and it's parser aren't called.

Ok, I understand it now.

<name> ALIAS FOR $n;

is to overcome the split between the two parser.

It is done by the backends main parser. For loadable
procedural languages, the main parser doesn't know anything
about the languages syntax or if the string given after AS is
a program text at all. It only creates the pg_proc tuple.

And another thing:

Does'nt the Oracle PL/SQL have both input and input/output parameters ?

It's more than a year since i last used it but I think that this is the case.

Hannu

jwieck@debis.com (Jan Wieck) wrote:

But I would like to have some discussion on language itself.
So I wrote down what I had in mind. The document is appended
below.

Please comment/suggest !

A question. Will it be possible to call functions from the PL/pgSQL? This
covers a fair bit, for example to evaluate expressions and casts etc as
well as calling user functions.

Or was this covered and I just missed it somehow?

-dg

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.

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.

It has input, input/output and output variables.

Michael
--
Dr. Michael Meskes, Projekt-Manager | topystem Systemhaus GmbH
meskes@topsystem.de | Europark A2, Adenauerstr. 20
meskes@debian.org | 52146 Wuerselen
Go SF49ers! Use Debian GNU/Linux! | Tel: (+49) 2405/4670-44

Hannu Krosing wrote:

And another thing:

Does'nt the Oracle PL/SQL have both input and input/output parameters ?

It's more than a year since i last used it but I think that this is the case.

Right, they have. But PostgreSQL doesn't and so PL/pgSQL
can't. PL/pgSQL cannot do any thing that a C function in
PostgreSQL cannot do. The PL handler is written in C and that
is what the executor calls instead of the compiled function.
From the backends point of view, a PL handler is just one C
function with a bunch of personalities.

Jan

--

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

David Gould wrote:

jwieck@debis.com (Jan Wieck) wrote:

But I would like to have some discussion on language itself.
So I wrote down what I had in mind. The document is appended
below.

Please comment/suggest !

A question. Will it be possible to call functions from the PL/pgSQL? This
covers a fair bit, for example to evaluate expressions and casts etc as
well as calling user functions.

Or was this covered and I just missed it somehow?

It is covered automagically by the way I'm implementing the
assignement. The assign code internally does a SELECT. And
thus:

n := count(*) from t1;

works (even if it's far from standard I think).

Jan

--

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

From my experience, it *** is *** impossible. I would be glad if somebody

told me different, but to my understanding the doc is wrong here.

Andreas

Andreas Zeugswetter wrote:

From my experience, it *** is *** impossible. I would be glad if somebody

told me different, but to my understanding the doc is wrong here.

Andreas

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.

Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
scope of this tutorial', but not impossible ;)

I have been thinking about that quite a time and when David
Gould said 'looks like PL functions want to _be_ SQL
functions', my first action was scratching my head. Then I
thought it would blow up the parser and executor with things
they wheren't designed for and dropped these thoughts.

Anyway - thanks David - it was a push into the right
direction. Not that I think PL functions should become real
SQL functions processed by the backends main parser and
executor. But they might become something between 'C' and
'SQL'.

The executor could check in ExecMakeFunctionResult() that the
function is a PL function (simply by looking at
fcache->func.fn_plhandler). So it is possible that the
Executor treats PL functions somewhat like
postquel_function().

My PL/pgSQL is now at the state, that I can write functions.
I'll leave triggers for later and take a look if that all is
possible. Would be really nice to enable tuples and sets of
tuples as PL return types and finally beeing able to do a

DECLARE ret EMP%ROWTYPE;
BEGIN
...
FOR ret IN SELECT ... LOOP
RETURN ret AND RESUME;
ENDLOOP;
...
END;

from PL/pgSQL. Well, returning and resume later might cause
some trouble with SPI, but let's see.

Jan

--

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

I wrote:

Andreas Zeugswetter wrote:

From my experience, it *** is *** impossible. I would be glad if somebody

told me different, but to my understanding the doc is wrong here.

Andreas

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.

Actually what I understood from the docs was thatit is 'terribly complicated' and 'beyond the
scope of this tutorial', but not impossible ;)

The executor could check in ExecMakeFunctionResult() that the
function is a PL function (simply by looking at
fcache->func.fn_plhandler). So it is possible that the
Executor treats PL functions somewhat like
postquel_function().

That way it might be possible. The call interface to the PL
handler must be extended and the PL handler cannot use SPI
any more. The memory management of SPI makes it impossible
to return from the function and resume later. Leaving the
function while connected to SPI would possibly corrupt the
SPI stack.

And the handling of functions returning tuples is really a
mess. The executor calls postquel_function() first to get a
tuple table slot, and then again for each attribute used via
nested dot expression. It's up to the function itself then to
create the correct projection from the functions targetlist.

And there must be something in the parser/optimizer too. For
an 'sql' function, the fcache has a prepared tuple table slot
on the actual call. But for 'C' or 'PL' functions, it is NULL
on the call to ExecMakeFunctionResult().

For now, I'll let the PL interface as is and try to finish
PL/pgSQL in the trigger area. Someday I'll get back to here
and give it a real try to enable tuples and sets as return
from functions. That all is far too much work for me right
now cause the next 4 weeks I have to do some homework (bought
a new house and that's built now).

Jan

--

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