Re: multiple CREATE FUNCTION AS items for PLs

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?

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(at)gmx(dot)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(at)postgresql(dot)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.

Peter Eisentraut <peter_e(at)gmx(dot)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

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

On 12/16/2012 07:03 PM, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)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
>
>

On 12/16/2012 07:20 PM, Hannu Krosing wrote:
> On 12/16/2012 07:03 PM, Tom Lane wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)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

Hannu Krosing <hannu(at)2ndQuadrant(dot)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

Hannu Krosing <hannu(at)2ndQuadrant(dot)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

On 12/16/2012 07:44 PM, Tom Lane wrote:
> Hannu Krosing <hannu(at)2ndQuadrant(dot)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

On 12/16/2012 07:37 PM, Tom Lane wrote:
> Hannu Krosing <hannu(at)2ndQuadrant(dot)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

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

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

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?

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.

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

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.

2012/12/17 Peter Eisentraut <peter_e(at)gmx(dot)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(at)postgresql(dot)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

Hannu Krosing <hannu(at)krosing(dot)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

On Mon, 2012-12-17 at 16:34 -0500, 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')

An alternative that has some amount of precedent in the Python world
would be to use comment pragmas, like this:

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

def helper_function(x):
...

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

$$;

The source code parser would look for this string on, say, the first two
lines, and then decide which way to process the source text.

This way we could get this done fairly easily without any new
infrastructure outside the language handler.

2012/12/28 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On Mon, 2012-12-17 at 16:34 -0500, 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')
>
> An alternative that has some amount of precedent in the Python world
> would be to use comment pragmas, like this:
>
> CREATE FUNCTION foo(a,b,c) AS $$
> # plpython: module
> import x
> from __future__ import nex_cool_feature
>
> def helper_function(x):
> ...
>
> def __pg_main__(a,b,c):
> defined function body here
>
> $$;
>
> The source code parser would look for this string on, say, the first two
> lines, and then decide which way to process the source text.
>
> This way we could get this done fairly easily without any new
> infrastructure outside the language handler.

this concept looks like more stronger and cleaner

+1

I thing so same idea is used in PL/v8

Regards

Pavel

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

On 12/28/2012 09:15 AM, Peter Eisentraut wrote:
> On Mon, 2012-12-17 at 16:34 -0500, 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')
> An alternative that has some amount of precedent in the Python world
> would be to use comment pragmas, like this:
>
> CREATE FUNCTION foo(a,b,c) AS $$
> # plpython: module
You mean something along the lines of how source encoding is
defined in http://www.python.org/dev/peps/pep-0263/ ?

Sounds like a plan :)
> import x
> from __future__ import nex_cool_feature
>
> def helper_function(x):
> ...
>
> def __pg_main__(a,b,c):
> defined function body here
>
> $$;
>
> The source code parser would look for this string on, say, the first two
> lines, and then decide which way to process the source text.
>
> This way we could get this done fairly easily without any new
> infrastructure outside the language handler.
>
I would still not name the defined function with any special static name
(like the samples __pg_main__) but have it default to the name of
PostgreSQL-defined function with possibility to override :

I don't much like the name __pg_main__ for the exported function what
about __call__ ?

__call__ is used when making an object callable in python, but is not
used at module
level so modules can't be callables in python.

Maybe just export the function with the same name as is defined in
CREATE FUNCTION ?

And in case we do want to override it, call it

CREATE FUNCTION foo(a int,b int, c text) AS $$
# plpython: module modulename
import x,y,z

def foo(a,b,c):
return a + b + c

def foo2(a,b,c):
return foo(a,b,int(c))

__call__ = foo2

$$ language plpythonu

If we always require the export using __call__ in new-style pl/python
functions we could
leave out the "# plpython: module " part altogether and just test for
__call__ in the compiled
modules namespace after trying to compile the source code as module and
re-compile
using current methods if this fails.

To make this fast also for old-style functions, we should store compiled
bytecode (.pyc) in
database as well, perhaps putting it in pg_proc.probin as a base64
encoded string (probin
is not used fro non-C functions) or adding a new bytea column
pg_proc.procode especially for this.

the module name could default to something more predictable, like
function name + argtypes
so that it would become exportable by other plpython functions

thus

CREATE FUNCTION foo(a int,b int, c text)

would create module

plpy.modules.foo_int4_int4_text

and

CREATE FUNCTION foo()

would become simply

plpy.modules.foo

in this case we could reuse the code so that if foo(a,b,c) from the previous example needs to be exported it would be just

CREATE FUNCTION foo(a int,b int, c int) AS $$
from plpy.modules.foo_int4_int4_text import foo2
__call__ = foo2
$$ language plpythonu

or even simpler

CREATE FUNCTION foo(a int,b int, c int) AS $$
from plpy.modules.foo_int4_int4_text import foo2 as __call__
$$ language plpythonu

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

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

On 12/28/2012 03:15 AM, Peter Eisentraut wrote:
> On Mon, 2012-12-17 at 16:34 -0500, 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')
> An alternative that has some amount of precedent in the Python world
> would be to use comment pragmas, like this:
>
> CREATE FUNCTION foo(a,b,c) AS $$
> # plpython: module
> import x
> from __future__ import nex_cool_feature
>
> def helper_function(x):
> ...
>
> def __pg_main__(a,b,c):
> defined function body here
>
> $$;
>
> The source code parser would look for this string on, say, the first two
> lines, and then decide which way to process the source text.
>
> This way we could get this done fairly easily without any new
> infrastructure outside the language handler.
>

Quite true, but I have wanted something along the lines of function
attributes for a long time (can't find the email thread right now, but I
do seem to recall it being discussed in some form), so I think there's a
good case for the OPTIONS mechanism.

Making the language handler pull this out of the function text seems a
bit ugly too.

cheers

andrew

> On 12/28/2012 09:15 AM, Peter Eisentraut wrote:
>> An alternative that has some amount of precedent in the Python world
>> would be to use comment pragmas, like this: ...
>> This way we could get this done fairly easily without any new
>> infrastructure outside the language handler.

+1 for not cluttering the generic CREATE FUNCTION syntax for this.
I note the parallel to plpgsql's #option syntax, too.

Hannu Krosing <hannu(at)krosing(dot)net> writes:
> To make this fast also for old-style functions, we should store compiled
> bytecode (.pyc) in
> database as well, perhaps putting it in pg_proc.probin as a base64
> encoded string (probin
> is not used fro non-C functions) or adding a new bytea column
> pg_proc.procode especially for this.

That might or might not be worth doing. In the absence of evidence that
plpython byte-compiling represents significant overhead for normal use,
it sounds a lot like premature optimization to me. In any case, it
should certainly not be done as part of the same patch Peter is thinking
about.

regards, tom lane

On 12/28/2012 09:15 AM, Peter Eisentraut wrote:
> On Mon, 2012-12-17 at 16:34 -0500, 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')
> An alternative that has some amount of precedent in the Python world
> would be to use comment pragmas, like this:
>
> CREATE FUNCTION foo(a,b,c) AS $$
> # plpython: module
> import x
> from __future__ import nex_cool_feature
>
> def helper_function(x):
> ...
>
> def __pg_main__(a,b,c):
> defined function body here
>
> $$;
>
> The source code parser would look for this string on, say, the first two
> lines, and then decide which way to process the source text.
>
> This way we could get this done fairly easily without any new
> infrastructure outside the language handler.
Peter, are you expecting to make write this patch ?

If so, then the very same approach (except the comment pragma
magic which is not needed there) is already done in python-postgres/be plpy
language handler. ( git://github.com/python-postgres/be.git )
Except there the modules exported function is named __main__ :)

And as a matter of bikeshedding I'd still prefer to name exported
function __call__ .

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

On 12/28/12 7:09 AM, Hannu Krosing wrote:
> Maybe just export the function with the same name as is defined in
> CREATE FUNCTION ?
>
> And in case we do want to override it, call it
>
> CREATE FUNCTION foo(a int,b int, c text) AS $$
> # plpython: module modulename
> import x,y,z
>
> def foo(a,b,c):
> return a + b + c
>
> def foo2(a,b,c):
> return foo(a,b,int(c))
>
> __call__ = foo2
>
> $$ language plpythonu

I like this approach, except I would call the attribute something like
__plpy_something___.

> If we always require the export using __call__ in new-style pl/python
> functions we could
> leave out the "# plpython: module " part altogether and just test for
> __call__ in the compiled
> modules namespace after trying to compile the source code as module and
> re-compile
> using current methods if this fails.

That's a thought. I'm not yet sure whether I prefer it.

On Fri, Dec 28, 2012 at 7:09 AM, Hannu Krosing <hannu(at)krosing(dot)net> wrote:
> thus
>
> CREATE FUNCTION foo(a int,b int, c text)
>
> would create module
>
> plpy.modules.foo_int4_int4_text

I don't know much of anything about Python, but keep in mind that
types can be renamed. It seems like that could break things if the
type name is included in the module name.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Dec 28, 2012 at 12:24 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2012/12/28 Peter Eisentraut <peter_e(at)gmx(dot)net>:
>> On Mon, 2012-12-17 at 16:34 -0500, 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')
>>
>> An alternative that has some amount of precedent in the Python world
>> would be to use comment pragmas, like this:
>>
>> CREATE FUNCTION foo(a,b,c) AS $$
>> # plpython: module
>> import x
>> from __future__ import nex_cool_feature
>>
>> def helper_function(x):
>> ...
>>
>> def __pg_main__(a,b,c):
>> defined function body here
>>
>> $$;
>>
>> The source code parser would look for this string on, say, the first two
>> lines, and then decide which way to process the source text.
>>
>> This way we could get this done fairly easily without any new
>> infrastructure outside the language handler.
>
> this concept looks like more stronger and cleaner
>
> +1
>
> I thing so same idea is used in PL/v8
>

What part of PL/v8 do you think is same?? It parses the body as other
PLs do and nothing special. plv8 also wanted this notion of
"function setting" before introducing find_function(), which natively
loads other JS functions that are defined via CREATE FUNCTION. Of
course it is not optimal, but it turned out it is not terribly slow.
Maybe it depends on language runtime. So for now, PL/v8 is managing
to do it and happy with the existing mechanism. It could be improved
somehow, but I don't want it to be complicated than now in order to
improve small stuff.

Thanks,
--
Hitoshi Harada