multiple CREATE FUNCTION AS items for PLs

Hello

I understand to motivation, but proposed syntax is not too intuitive and robust

can you do it in one function and call import only in first call?

Regards

Pavel

2012/12/16 Peter Eisentraut <peter_e@gmx.net>:

I'm going to use PL/Python as an example, but I would also like to know
if this could be applicable to other languages.

When you do

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
source code here
$$;

it internally creates a "source file" that contains

---
def __plpython_procedure_foo_12345():
source code here
---

It would be useful to be able to do something like this instead:

---
some code here

def __plpython_procedure_foo_12345():
some more code here
---

This would especially be useful for placing imports into the first part.
While you can have them in the function definition, that means they are
executed every time the function is called, which makes it much slower.
Also, future imports are not possible this way.

CREATE FUNCTION already supports multiple AS items. Currently, multiple
AS items are rejected for all languages but C. I'd imagine lifting that
restriction and leaving it up to the validator to check it. Then any
language can accept two AS items if it wants and paste them together in
whichever way it needs. (The probin/prosrc naming will then become more
obsolete, but it's perhaps not worth changing anything about that.)

So in practice this might look like this:

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
import x
import y
$$,
$$
real code here
$$;

Comments?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, 2012-12-16 at 10:20 +0100, Pavel Stehule wrote:

Hello

I understand to motivation, but proposed syntax is not too intuitive and robust

can you do it in one function and call import only in first call?

Sometimes, but it's even less intuitive and robust.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Eisentraut <peter_e@gmx.net> writes:

When you do

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
source code here
$$;

it internally creates a "source file" that contains

---
def __plpython_procedure_foo_12345():
source code here
---

It would be useful to be able to do something like this instead:

---
some code here

def __plpython_procedure_foo_12345():
some more code here
---

This would especially be useful for placing imports into the first part.

Sure, but wouldn't it be cleaner to do that via some language-specific
syntax inside the function string? I'm imagining some syntax like

CREATE FUNCTION ... AS $$
global[ some definitions here ]
function code here
$$;

where the PL would be responsible for pulling off the "global" chunk
and structuring what it outputs accordingly.

CREATE FUNCTION already supports multiple AS items. Currently, multiple
AS items are rejected for all languages but C. I'd imagine lifting that
restriction and leaving it up to the validator to check it. Then any
language can accept two AS items if it wants and paste them together in
whichever way it needs. (The probin/prosrc naming will then become more
obsolete, but it's perhaps not worth changing anything about that.)

I think doing it this way is a bad idea, mainly because (1) it won't
scale to more than two items (at least not without great rearrangement
of pg_proc) and (2) having two otherwise-unlabeled AS items isn't at all
understandable or readable. For instance, which of the two is the
global part, and why? The fact that C functions do it like that is a
legacy syntax we're stuck with, not a good model to copy for other
languages.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 07:37 AM, Peter Eisentraut wrote:

I'm going to use PL/Python as an example, but I would also like to know
if this could be applicable to other languages.

When you do

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
source code here
$$;

it internally creates a "source file" that contains

---
def __plpython_procedure_foo_12345():
source code here
---

It would be useful to be able to do something like this instead:

---
some code here

def __plpython_procedure_foo_12345():
some more code here
---

This would especially be useful for placing imports into the first part.
While you can have them in the function definition, that means they are
executed every time the function is called, which makes it much slower.
Also, future imports are not possible this way.

CREATE FUNCTION already supports multiple AS items. Currently, multiple
AS items are rejected for all languages but C. I'd imagine lifting that
restriction and leaving it up to the validator to check it. Then any
language can accept two AS items if it wants and paste them together in
whichever way it needs. (The probin/prosrc naming will then become more
obsolete, but it's perhaps not worth changing anything about that.)

So in practice this might look like this:

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
import x
import y
$$,
$$
real code here
$$;

Comments?

As an idea seems quite good, but maybe the "run once" part could use its
own keyword in the future, something like PREPARE or REQUIRE?

Or maye WITH to reuse a keyword

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
WITH -- this part is evaluated only once, in PLy_procedure_create
$$
import x
import y
$$
AS -- this is compiled in the same namespace as above
$$
<function body here>
$$;

WHile at it, why not also fix the functions to be real function
_with_ _real_ _arguments_ , not arguments-passed-in-as-globals

and at least we could call this function with its real name inside its own module
(stored global namespace) so we could easily do recursion

CREATE FUNCTION factorial(n bigint) returns bigint LANGUAGE plpythonu
AS $$
if n==0: return 1
return factorial(n-1) * n
$$;

----------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 07:03 PM, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

When you do
CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
source code here
$$;
it internally creates a "source file" that contains
---
def __plpython_procedure_foo_12345():
source code here
---
It would be useful to be able to do something like this instead:
---
some code here
def __plpython_procedure_foo_12345():
some more code here
---
This would especially be useful for placing imports into the first part.

Sure, but wouldn't it be cleaner to do that via some language-specific
syntax inside the function string? I'm imagining some syntax like

CREATE FUNCTION ... AS $$
global[ some definitions here ]
function code here
$$;

where the PL would be responsible for pulling off the "global" chunk
and structuring what it outputs accordingly.

I was going to suggest some special function name to be pulled out of code
passed to CREATE FUNCTION in line with

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def __pg_main__(a,b,c):
defined function body here

$$;

so that the whole text gets compiled into module at first call and the __pg_main__ will
be the function that gets called as foo(a,b,c) from postgresql

but this would not be backwards compatible, at least not in any obvious way.

-------------------
Hanniu

CREATE FUNCTION already supports multiple AS items. Currently, multiple
AS items are rejected for all languages but C. I'd imagine lifting that
restriction and leaving it up to the validator to check it. Then any
language can accept two AS items if it wants and paste them together in
whichever way it needs. (The probin/prosrc naming will then become more
obsolete, but it's perhaps not worth changing anything about that.)

I think doing it this way is a bad idea, mainly because (1) it won't
scale to more than two items (at least not without great rearrangement
of pg_proc) and (2) having two otherwise-unlabeled AS items isn't at all
understandable or readable. For instance, which of the two is the
global part, and why? The fact that C functions do it like that is a
legacy syntax we're stuck with, not a good model to copy for other
languages.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 07:20 PM, Hannu Krosing wrote:

On 12/16/2012 07:03 PM, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

When you do
CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
source code here
$$;
it internally creates a "source file" that contains
---
def __plpython_procedure_foo_12345():
source code here
---
It would be useful to be able to do something like this instead:
---
some code here
def __plpython_procedure_foo_12345():
some more code here
---
This would especially be useful for placing imports into the first
part.

Sure, but wouldn't it be cleaner to do that via some language-specific
syntax inside the function string? I'm imagining some syntax like

CREATE FUNCTION ... AS $$
global[ some definitions here ]
function code here
$$;

where the PL would be responsible for pulling off the "global" chunk
and structuring what it outputs accordingly.

I was going to suggest some special function name to be pulled out of
code
passed to CREATE FUNCTION in line with

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def __pg_main__(a,b,c):
defined function body here

$$;

so that the whole text gets compiled into module at first call and the
__pg_main__ will
be the function that gets called as foo(a,b,c) from postgresql

On further thought the function name should just be what it is defined
in postgresql, like this

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def foo(a,b,c):
defined function body here

def bar(i,j):
function body for bar(i,j)
$$ language plpythonu;

if the above definition saved the whole compiled unit as module
pg_functions.foo then
we could define postgresql/plpython function bar() by importing the same
module

CREATE FUNCTION bar(a,b,c) AS $$
form pg_functions.foo import bar
$$ language plpythonu;

This is not as simple as this, as we still need to find the source for
foo in case bar() gets
called first and module foo is not yet saved, but this could be one
approach to having
python modules without introducing extra syntax at postgreSQL level.

but this would not be backwards compatible, at least not in any
obvious way.

This is still unfortunately true :(

------------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hannu Krosing <hannu@2ndQuadrant.com> writes:

On further thought the function name should just be what it is defined
in postgresql, like this

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def foo(a,b,c):
defined function body here

def bar(i,j):
function body for bar(i,j)
$$ language plpythonu;

but this would not be backwards compatible, at least not in any
obvious way.

This is still unfortunately true :(

Could we say that *if* the function text contains a line beginning
"def function_name" then we interpret it as above, otherwise oldstyle?
I'm not sure how big a risk of false positives there'd be.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hannu Krosing <hannu@2ndQuadrant.com> writes:

On further thought the function name should just be what it is defined
in postgresql, like this

CREATE FUNCTION foo(a,b,c) AS $$
def foo(a,b,c):

BTW, how well will that play with overloaded function names? I don't
particularly care for saying that PL/Python fails if you overload a
function name across multiple schemas or argument lists ...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 07:44 PM, Tom Lane wrote:

Hannu Krosing <hannu@2ndQuadrant.com> writes:

On further thought the function name should just be what it is defined
in postgresql, like this
CREATE FUNCTION foo(a,b,c) AS $$
def foo(a,b,c):

BTW, how well will that play with overloaded function names? I don't
particularly care for saying that PL/Python fails if you overload a
function name across multiple schemas or argument lists ...

Currently each pl/python function gets compiled in its own python
module namespace, so this is not be a problem .

--------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 07:37 PM, Tom Lane wrote:

Hannu Krosing <hannu@2ndQuadrant.com> writes:

On further thought the function name should just be what it is defined
in postgresql, like this
CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature
def helper_function(x):
...
def foo(a,b,c):
defined function body here
def bar(i,j):
function body for bar(i,j)
$$ language plpythonu;

but this would not be backwards compatible, at least not in any
obvious way.

This is still unfortunately true :(

Could we say that *if* the function text contains a line beginning
"def function_name" then we interpret it as above, otherwise oldstyle?
I'm not sure how big a risk of false positives there'd be.

You could be inclined to define a recursive function like this under
current pl/python

CREATE FUNCTION factorial(n bigint) returns bigint LANGUAGE plpythonu
AS $$

def factorial(n):
if n==0: return 1
return factorial(n-1) * n

return factorial(n)

$$;

but at least for functions returning a non-null value an old-style
definition usually
end with line in form

return <something>

------------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 01:37 AM, Peter Eisentraut wrote:

So in practice this might look like this:

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
import x
import y
$$,
$$
real code here
$$;

Bleah.

It seems obscure to say the least.

Why not have something along the lines of plperl's on_init setting to
load libraries? Among other things that would give you the advantage of
being able to preload them, and also of some consistency among PLs.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 08:44 PM, Andrew Dunstan wrote:

On 12/16/2012 01:37 AM, Peter Eisentraut wrote:

So in practice this might look like this:

CREATE FUNCTION foo(...) ... LANGUAGE plpythonu
AS $$
import x
import y
$$,
$$
real code here
$$;

Bleah.

It seems obscure to say the least.

Why not have something along the lines of plperl's on_init setting to
load libraries? Among other things that would give you the advantage
of being able to preload them, and also of some consistency among PLs.

While plpython.on_init is a much needed feature, it is orthogonal to
what is discussed here.

AIUI Peters proposal aimed adding per-function preparation /
initialisation not something
to be run for initialising the whole interpreter.

Also - to make the plpython.on_init really useful - there should be some
way to have python
_modules_ inside postgresql

If we would redefine plpython functions to define their own _visible_
modules (currently
each has its own module but there is no way to reference it from others)
and have the
function stored in this module we could not only solve the problem of
plpython modules
but also for calling other plpython modules directly from python .

for example, if doing

CREATE FUNCTION foo(i int) RETURNS int LANGUAGE plpythonu $$
def foo(i):
return i+1
$$;

would also make this function available as plpy.modules.foo_int.foo
(meaning its global
namespace would be saved as plpy.modules.foo_int

then other plpy functions could call it directly by doing

from plpy.modules.foo_int import foo

I try to come up with a more detailed proposal along these lines.

----------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, 2012-12-16 at 19:13 +0100, Hannu Krosing wrote:

As an idea seems quite good, but maybe the "run once" part could use
its
own keyword in the future, something like PREPARE or REQUIRE?

Well, either we do it in a language independent way, in which case this
would be too prescriptive, or we do it in a Python-specific way (less
likely), but "prepare" or "require" are not Python concepts.

WHile at it, why not also fix the functions to be real function
_with_ _real_ _arguments_ , not arguments-passed-in-as-globals

and at least we could call this function with its real name inside its
own module
(stored global namespace) so we could easily do recursion

CREATE FUNCTION factorial(n bigint) returns bigint LANGUAGE plpythonu
AS $$
if n==0: return 1
return factorial(n-1) * n
$$;

These are also good things to fix, but are they related? Could they not
be fixed independently?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, 2012-12-16 at 13:03 -0500, Tom Lane wrote:

Sure, but wouldn't it be cleaner to do that via some language-specific
syntax inside the function string? I'm imagining some syntax like

CREATE FUNCTION ... AS $$
global[ some definitions here ]
function code here
$$;

where the PL would be responsible for pulling off the "global" chunk
and structuring what it outputs accordingly.

But then the language text wouldn't be Python anymore.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/2012 10:23 PM, Peter Eisentraut wrote:

On Sun, 2012-12-16 at 19:13 +0100, Hannu Krosing wrote:

As an idea seems quite good, but maybe the "run once" part could use
its
own keyword in the future, something like PREPARE or REQUIRE?

Well, either we do it in a language independent way, in which case this
would be too prescriptive, or we do it in a Python-specific way (less
likely), but "prepare" or "require" are not Python concepts.

The proposed keywords names are meant to be language-independant
and to signify the the part that is meant for initialisation or
requirements.

The multiple AS $$...$$ sections have to mean something to be useful at all.

My final choce of WITH seem to both fit with run-one/required/init meaning
and is already a keyword.

But I'd ended preferring much more the approach of putting the whole
function module in functions source code and returning as the plpython
function the item matching of the defined function which can be a function
or any other callable.

The main problem is staying backwards compatible with existing
implementation.

WHile at it, why not also fix the functions to be real function
_with_ _real_ _arguments_ , not arguments-passed-in-as-globals

and at least we could call this function with its real name inside its
own module
(stored global namespace) so we could easily do recursion

CREATE FUNCTION factorial(n bigint) returns bigint LANGUAGE plpythonu
AS $$
if n==0: return 1
return factorial(n-1) * n
$$;

These are also good things to fix, but are they related? Could they not
be fixed independently?

They could, but fixing these together will probably result in a cleaner
design :)

even with your original multiple-code-strings design you end up
manipulating
function-global namespaces (which seem really close to modules) to put the
first $$...$$ there as run-once, pre-def code.

using functions real name (instead of _plpython_<funcname>_<oid>) in its
module
namespace is an one-line fix but to be really useful the mess with
arguments-as-globals
needs to be rectified.

if we move to the function-code-as module approach we will no longer need
to munge code (add def .... before code and then \t at the beginning of
each line)
which makes everything much cleaner.

The main thing to solve is different model for passing function
arguments at call time.

---------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/16/12 1:20 PM, Hannu Krosing wrote:

I was going to suggest some special function name to be pulled out of code
passed to CREATE FUNCTION in line with

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def __pg_main__(a,b,c):
defined function body here

$$;

so that the whole text gets compiled into module at first call and the
__pg_main__ will
be the function that gets called as foo(a,b,c) from postgresql

but this would not be backwards compatible, at least not in any obvious way.

Yes, this would be a good solution for some applications, but the only
way I can think of to manage the compatibility issue is to invent some
function attribute system like

CREATE FUNCTION ... OPTIONS (call_convention 'xyz')

But this is also a lot more typing, so the two-part AS solution still
has some appeal if you just want an import.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2012/12/17 Peter Eisentraut <peter_e@gmx.net>:

On 12/16/12 1:20 PM, Hannu Krosing wrote:

I was going to suggest some special function name to be pulled out of code
passed to CREATE FUNCTION in line with

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def __pg_main__(a,b,c):
defined function body here

$$;

so that the whole text gets compiled into module at first call and the
__pg_main__ will
be the function that gets called as foo(a,b,c) from postgresql

but this would not be backwards compatible, at least not in any obvious way.

Yes, this would be a good solution for some applications, but the only
way I can think of to manage the compatibility issue is to invent some
function attribute system like

CREATE FUNCTION ... OPTIONS (call_convention 'xyz')

But this is also a lot more typing, so the two-part AS solution still
has some appeal if you just want an import.

two-part AS is not intuitive and it is looking really obscure

Regards

Pavel

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/17/2012 10:34 PM, Peter Eisentraut wrote:

On 12/16/12 1:20 PM, Hannu Krosing wrote:

I was going to suggest some special function name to be pulled out of code
passed to CREATE FUNCTION in line with

CREATE FUNCTION foo(a,b,c) AS $$
import x
from __future__ import nex_cool_feature

def helper_function(x):
...

def __pg_main__(a,b,c):
defined function body here

$$;

so that the whole text gets compiled into module at first call and the
__pg_main__ will
be the function that gets called as foo(a,b,c) from postgresql

but this would not be backwards compatible, at least not in any obvious way.

Yes, this would be a good solution for some applications, but the only
way I can think of to manage the compatibility issue is to invent some
function attribute system like

CREATE FUNCTION ... OPTIONS (call_convention 'xyz')

How about using a GUC for setting calling convention?

This SET can be even done as part of CREATE FUNCTION .

CREATE FUNCTION $$ ... $$ ... SET plpython.cc=9.2;

---------------------
Hannu

But this is also a lot more typing, so the two-part AS solution still
has some appeal if you just want an import.

Import is just a small sub-case of what you would want in your module
environment. And as it is really done only once per backend anyway -
starting the second time it is just a dictionary lookup - it should not be
singled out, at least not for performance reasons.

-----------------------------
Hannu

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hannu Krosing <hannu@krosing.net> writes:

On 12/17/2012 10:34 PM, Peter Eisentraut wrote:

Yes, this would be a good solution for some applications, but the only
way I can think of to manage the compatibility issue is to invent some
function attribute system like

CREATE FUNCTION ... OPTIONS (call_convention 'xyz')

How about using a GUC for setting calling convention?

GUCs are a truly bad fit for properties that need to be function-local.

This SET can be even done as part of CREATE FUNCTION .
CREATE FUNCTION $$ ... $$ ... SET plpython.cc=9.2;

That doesn't fix the problem, because a setting made that way will
affect called functions too. The only way you could use it safely
would be to add such a SET clause to every single plpython function
in the database. At that point an OPTIONS clause (which can have a
backwards-compatible default behavior) looks a lot more attractive.

I still think that embedding some type of extension syntax in the
function body is the best solution. But if that's too ugly, let's
invent something like Peter's OPTIONS syntax above, with the
understanding that it affects only the function it's applied to
(unlike SET), and contains PL-specific options.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers