on the patches list, I posted the first files
for suuport for using perl as the procedural
language.

The makefile uses libtool (probably incorrectly).

It is not 'safe'.

Perl XS modules cannot be used.

Using Perl in rules hasn't even been thought about.

IF the code looks suspiciously like Jan's code
for pltcl - well, it _is_ Jan's code for pltcl
horribly mangled.

It compiles and runs for me. YMMV.

more code and docs to follow.
-- 
Mark Hollomon
mhh@mindspring.com

Fri, 23 Jul 1999 16:15:10 -0400
Mark Hollomon <mhh@mindspring.com>
plperl intial pass
Great,

just compiled and install but need to look at some examples :-)
btw, here is a patch for createlang command to enable plperl
I'm not sure about trusted field.

--- createlang  Sat Jul 24 22:27:05 1999
+++ /usr/local/pgsql/bin/createlang     Wed Jul 21 19:36:55 1999
@@ -84,9 +84,6 @@
        plpgsql)        lancomp="PL/pgSQL"
                                trusted="TRUSTED"
                                handler="plpgsql_call_handler";;
-       plperl)         lancomp="PL/Perl"
-                               trusted="TRUSTED"
-                               handler="plperl_call_handler";;
        pltcl)          lancomp="PL/Tcl"
                                trusted="TRUSTED"
                                handler="pltcl_call_handler";;


On Fri, 23 Jul 1999, Mark Hollomon wrote:

> Date: Fri, 23 Jul 1999 16:15:10 -0400
> From: Mark Hollomon <mhh@mindspring.com>
> To: pgsql-hackers@postgreSQL.org
> Subject: [HACKERS] plperl intial pass
> 
> on the patches list, I posted the first files
> for suuport for using perl as the procedural
> language.
> 
> The makefile uses libtool (probably incorrectly).
> 
> It is not 'safe'.
> 
> Perl XS modules cannot be used.
> 
> Using Perl in rules hasn't even been thought about.
> 
> IF the code looks suspiciously like Jan's code
> for pltcl - well, it _is_ Jan's code for pltcl
> horribly mangled.
> 
> It compiles and runs for me. YMMV.
> 
> more code and docs to follow.
> -- 
> Mark Hollomon
> mhh@mindspring.com
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Sat, 24 Jul 1999 22:26:33 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] plperl intial pass
On Sat, Jul 24, 1999 at 10:26:33PM +0400, Oleg Bartunov wrote:
> Great,
> 
> just compiled and install but need to look at some examples :-)
> btw, here is a patch for createlang command to enable plperl
> I'm not sure about trusted field.

At this point it definitely should not be trusted. (Trust me).

How about the famous hello world:

create function hello () returns text as '
return "Hello world!";' language 'plperl';

or a quick sum:

create function sum2 (int4, int4) returns int4 as '
$_[0] + $_[1];' language 'plperl';

The args are in @_ (naturally). Tuples are passed as
hash references.

Access to SPI functionality is coming.



-- 
Mark Hollomon
mhh@mindspring.com

Sun, 25 Jul 1999 12:08:51 -0400
Mark Hollomon <mhh@mindspring.com>
Re: [HACKERS] plperl intial pass
Great !

at least your examples works:
test=> select hello();
NOTICE:  plperl_func_handler: have been asked to call __PLperl_proc_329289
NOTICE:  plperl_func_handler: __PLperl_proc_329289 was in the hash
NOTICE:  plperl_call_perl_func: calling __PLperl_proc_329289
NOTICE:  plperl_func_handler: returned from plperl_call_perl_func
NOTICE:  plperl_func_handler: return as string = Hello world!
NOTICE:  plperl_func_handler: Datum is 826ee30
hello       
------------
Hello world!
(1 row)
test=> create function sum2 (int4, int4) returns int4 as '
test'> $_[0] + $_[1];' language 'plperl';
CREATE
test=> select sum2(4,2);
NOTICE:  plperl_func_handler: have been asked to call __PLperl_proc_329290
NOTICE:  plperl_func_handler: __PLperl_proc_329290 doesn't exist yet
NOTICE:  plperl_create_sub: creating the sub
NOTICE:  plperl_call_perl_func: calling __PLperl_proc_329290
NOTICE:  plperl_func_handler: returned from plperl_call_perl_func
NOTICE:  plperl_func_handler: return as string = 6
NOTICE:  plperl_func_handler: Datum is 6
sum2
----
   6
(1 row)

	Regards,
		
		Oleg
On Sun, 25 Jul 1999, Mark Hollomon wrote:

> Date: Sun, 25 Jul 1999 12:08:51 -0400
> From: Mark Hollomon <mhh@mindspring.com>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] plperl intial pass
> 
> On Sat, Jul 24, 1999 at 10:26:33PM +0400, Oleg Bartunov wrote:
> > Great,
> > 
> > just compiled and install but need to look at some examples :-)
> > btw, here is a patch for createlang command to enable plperl
> > I'm not sure about trusted field.
> 
> At this point it definitely should not be trusted. (Trust me).
> 
> How about the famous hello world:
> 
> create function hello () returns text as '
> return "Hello world!";' language 'plperl';
> 
> or a quick sum:
> 
> create function sum2 (int4, int4) returns int4 as '
> $_[0] + $_[1];' language 'plperl';
> 
> The args are in @_ (naturally). Tuples are passed as
> hash references.
> 
> Access to SPI functionality is coming.
> 
> 
> 
> -- 
> Mark Hollomon
> mhh@mindspring.com
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Mon, 26 Jul 1999 04:28:39 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] plperl intial pass

On 24-Jul-99 Oleg Bartunov wrote:
> Great,
> 
> just compiled and install but need to look at some examples :-)
> btw, here is a patch for createlang command to enable plperl
> I'm not sure about trusted field.

Does plperl use Perl interpreter or it's completly different language 
with similar syntax?

Do you have some speed/memory statistic or plpgsql/plperl comparison ?

---
Dmitry Samersoff, dms@wplus.net, ICQ:3161705
http://devnull.wplus.net
* There will come soft rains ...

Mon, 26 Jul 1999 12:19:56 +0400 (MSD)
Dmitry Samersoff <dms@wplus.net>
Re: [HACKERS] plperl intial pass
>
> Great,
>
> just compiled and install but need to look at some examples :-)
> btw, here is a patch for createlang command to enable plperl
> I'm not sure about trusted field.
>
> --- createlang  Sat Jul 24 22:27:05 1999
> +++ /usr/local/pgsql/bin/createlang     Wed Jul 21 19:36:55 1999
> @@ -84,9 +84,6 @@
>         plpgsql)        lancomp="PL/pgSQL"
>                                 trusted="TRUSTED"
>                                 handler="plpgsql_call_handler";;
> -       plperl)         lancomp="PL/Perl"
> -                               trusted="TRUSTED"
> -                               handler="plperl_call_handler";;
>         pltcl)          lancomp="PL/Tcl"
>                                 trusted="TRUSTED"
>                                 handler="pltcl_call_handler";;

    I  wouldn't  make  it  a  TRUSTED language right now, because
    until PL/Perl has a safe mode (what Mark said it hasn't  now)
    it  is  a  security  hole.   Unpriviliged  users could create
    functions in PL/Perl that modify the hba.conf!


Jan

--

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



Mon, 26 Jul 1999 11:15:25 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] plperl intial pass
Dmitry Samersoff wrote:

>
> On 24-Jul-99 Oleg Bartunov wrote:
> > Great,
> >
> > just compiled and install but need to look at some examples :-)
> > btw, here is a patch for createlang command to enable plperl
> > I'm not sure about trusted field.
>
> Does plperl use Perl interpreter or it's completly different language
> with similar syntax?
>
> Do you have some speed/memory statistic or plpgsql/plperl comparison ?

    It uses a real Perl precompiler/interpreter inside.

    I  think  it's  far  too early for such comparisions. As Mark
    wrote, PL/Perl's SPI interface  (for  accessing  tables  from
    inside a function) is still to come, and if I remember right,
    triggers are another delayed feature up to now.

    When it's done, I would expect that PL/Perl could  outperform
    PL/pgSQL  in  many  cases.  I  haven't done speed comparision
    between PL/pgSQL  and  PL/Tcl  yet,  but  I  know  all  their
    internals.  The reason for my assumtion is that PL/pgSQL uses
    the PostgreSQL executor for all computations.  That's IMHO  a
    pro,  because it assures that any defined datatype, function,
    operator and aggregate is automagically available in PL/pgSQL
    and  all  computations  return  exactly the same result as if
    they're done inside an SQL statement. But nothing on earth is
    for free, not even the death - you pay for it with your life.


Jan

--

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



Mon, 26 Jul 1999 11:50:52 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] plperl intial pass
Mark Hollomon wrote:

>
> on the patches list, I posted the first files
> for suuport for using perl as the procedural
> language.

    Congratulations!

>
> The makefile uses libtool (probably incorrectly).
>
> It is not 'safe'.
>
> Perl XS modules cannot be used.

    What's  an  XS  module?  If  it's a shared object dynamically
    linked - don't care too much - PL/Tcl cannot either.

>
> Using Perl in rules hasn't even been thought about.

    If a function works from a query, the same function must work
    too in a rule because the rewriter only mangles up parsetrees
    so when executing, they are invoked from a query. Or did  you
    mean triggers?

>
> IF the code looks suspiciously like Jan's code
> for pltcl - well, it _is_ Jan's code for pltcl
> horribly mangled.

    What ya think where the skeleton for PL/pgSQL came from :-) I
    just  wrote  my  own  SQL  scripting  bytecode  compiler  and
    executor and placed them into the PL/Tcl sources.

    Congrats again - great work - move on.


Jan

--

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



Mon, 26 Jul 1999 12:18:06 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] plperl intial pass
Dmitry Samersoff wrote:
> 
> On 24-Jul-99 Oleg Bartunov wrote:
> > Great,
> >
> > just compiled and install but need to look at some examples :-)
> > btw, here is a patch for createlang command to enable plperl
> > I'm not sure about trusted field.
> 
> Does plperl use Perl interpreter or it's completly different language
> with similar syntax?
> 

It imbeds the perl interpreter. Just as pltcl imbeds the tcl
interpreter.

> Do you have some speed/memory statistic or plpgsql/plperl comparison ?

No.

-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Mon, 26 Jul 1999 09:48:38 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: [HACKERS] plperl intial pass
Jan Wieck wrote:
> 
> Mark Hollomon wrote:
> 
> >
> > It is not 'safe'.
> >
> > Perl XS modules cannot be used.
> 
>     What's  an  XS  module?  If  it's a shared object dynamically
>     linked - don't care too much - PL/Tcl cannot either.

Correct. The problem is that the Opcode module, which allows you to
disable features of the compiler (to close security holes) is an
XS module. In theory, it is possible to do without Opcode, but
doing so would create a very heavy perl version dependency in plperl.

So, I have to get XS stuff working in order to disallow XS stuff.
sigh.

And plperl can never be trusted until I can forbid writing to the
filesystem.

> 
> >
> > Using Perl in rules hasn't even been thought about.
> 
>     If a function works from a query, the same function must work
>     too in a rule because the rewriter only mangles up parsetrees
>     so when executing, they are invoked from a query. Or did  you
>     mean triggers?

Ick. Correct. I meant triggers.


-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Mon, 26 Jul 1999 09:59:54 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: [HACKERS] plperl intial pass
Mark Hollomon wrote:
>
> Jan Wieck wrote:
> >
>
> Correct. The problem is that the Opcode module, which allows you to
> disable features of the compiler (to close security holes) is an
> XS module. In theory, it is possible to do without Opcode, but
> doing so would create a very heavy perl version dependency in plperl.
>
> So, I have to get XS stuff working in order to disallow XS stuff.
> sigh.
>
> And plperl can never be trusted until I can forbid writing to the
> filesystem.

    I  see.  Maybe  it's possible to get the Opcode stuff working
    without full XS? Adding full XS support only to disable it  -
    what an overkill :-)

    Correct  me  if I'm wrong (I'm only guessing). Like for Perl,
    the Tcl interpreter itself sits in a library. To  create  the
    standalone  tclsh, a small tclAppInit.c file is compiled into
    the tclsh executable.   The  default  one  only  creates  one
    interpreter  and  arranges  for  the  execution of the script
    given in argv[0] or starts up the interactive shell.

    A  dynamically  loadable  Tcl  module  contains  one  special
    function  named  <libname>_Init()  where  first  character of
    libname is capitalized.  On dynamic load,  this  function  is
    called  with  the  invoking  interpreter  as  argument.  This
    function then calls  Tcl_CreateCommand()  etc.  to  tell  Tcl
    what's   coming   here   and   does   other  module  specific
    initializations.

    It is now possible, to add other stuff to tclAppInit.c  (like
    calls  to  Mymodule_Init)  and link it against some more than
    libtcl.so. That was  the  standard  solution  before  dynamic
    loading  was  that  easy  as it is today (back in the days of
    a.out libs).

    Your plperl.c is mostly my pltcl.c - so I assume it does  the
    same  things  mainly.  Create  an  interpreter and throw some
    strings into it, hoping they are intelligable in some way (at
    least  produce  a  helpful error message).  Thus, it might be
    possible to add calls to the initializations for  the  Opcode
    XS  directly  into  the  plperl  module  after  creating  the
    interpreter and link it against Opcode as well.

    This is just the way I would do it for Tcl and I'll surely do
    it   someday.    I  would  like  to  have  a  second,  unsafe
    interpreter in the module.  That could then modify  files  or
    use  the  frontend  library to access a different database on
    another server. Needless to say that this then  would  be  an
    untrusted language, available only for db superusers.


Jan

--

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



Mon, 26 Jul 1999 17:45:19 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] plperl intial pass
Jan Wieck wrote:
> 
> Mark Hollomon wrote:
> >
> > Jan Wieck wrote:
> > >
> >
> > Correct. The problem is that the Opcode module, which allows you to
> > disable features of the compiler (to close security holes) is an
> > XS module. In theory, it is possible to do without Opcode, but
> > doing so would create a very heavy perl version dependency in plperl.
> >
> > So, I have to get XS stuff working in order to disallow XS stuff.
> > sigh.
> >
> > And plperl can never be trusted until I can forbid writing to the
> > filesystem.
> 
>     I  see.  Maybe  it's possible to get the Opcode stuff working
>     without full XS? Adding full XS support only to disable it  -
>     what an overkill :-)
> 
>     Correct  me  if I'm wrong (I'm only guessing). Like for Perl,
>     the Tcl interpreter itself sits in a library. To  create  the
>     standalone  tclsh, a small tclAppInit.c file is compiled into
>     the tclsh executable.   The  default  one  only  creates  one
>     interpreter  and  arranges  for  the  execution of the script
>     given in argv[0] or starts up the interactive shell.
> 
>     A  dynamically  loadable  Tcl  module  contains  one  special
>     function  named  <libname>_Init()  where  first  character of
>     libname is capitalized.  On dynamic load,  this  function  is
>     called  with  the  invoking  interpreter  as  argument.  This
>     function then calls  Tcl_CreateCommand()  etc.  to  tell  Tcl
                           ^^^^^^^^^^^^^^^^^

And here-in lies the problem. Tcl_CreateCommand is sitting, not
in the executable, but in the shared-lib with the function call
handler. dlopen(), by default will not link across shared-libs.

          postgres
     /-----/  \-----\
     |              |
  plperl.so ---> Opcode.so
             ^^
This link doesn't happen.

Passing RTLD_GLOBAL (I think) as a flag to dlopen makes the symbols
in a shared-lib available for linking into the next shared-lib.

But postgresql doesn't use the RTLD_GLOBAL flag and patching the
backend to load _everything_ with RTLD_GLOBAL seemed like it could
have less than desirable behavior.

a.out systems are easier since perl's dynamic loading subsystem
would take care of problem for me.

>     what's   coming   here   and   does   other  module  specific
>     initializations.
> 
>     It is now possible, to add other stuff to tclAppInit.c  (like
>     calls  to  Mymodule_Init)  and link it against some more than
>     libtcl.so. That was  the  standard  solution  before  dynamic
>     loading  was  that  easy  as it is today (back in the days of
>     a.out libs).

That is exactly how it works. But see above.

And on top of the above problem, postgres assumes all linuxen
use a.out type loading. Where as perl uses dlopen where it can.

Getting those two to play together is more than I care to attempt.
I am researching a fix now to let linux installations use dlopen
if it is available.

I would not be unhappy if somebody beats me to it.

>     This is just the way I would do it for Tcl and I'll surely do
>     it   someday.    I  would  like  to  have  a  second,  unsafe
>     interpreter in the module.  That could then modify  files  or
>     use  the  frontend  library to access a different database on
>     another server. Needless to say that this then  would  be  an
>     untrusted language, available only for db superusers.
> 

Yes, I've been thinking about that as well. It would be nice to have
permissions based on userid. Maybe the 'suid' stuff that is being
discussed in another thread will gives us a mechanism.

-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Mon, 26 Jul 1999 13:38:36 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: [HACKERS] plperl intial pass
Mark Hollomon wrote:

> >     A  dynamically  loadable  Tcl  module  contains  one  special
> >     function  named  <libname>_Init()  where  first  character of
> >     libname is capitalized.  On dynamic load,  this  function  is
> >     called  with  the  invoking  interpreter  as  argument.  This
> >     function then calls  Tcl_CreateCommand()  etc.  to  tell  Tcl
>                            ^^^^^^^^^^^^^^^^^
>
> And here-in lies the problem. Tcl_CreateCommand is sitting, not
> in the executable, but in the shared-lib with the function call
> handler. dlopen(), by default will not link across shared-libs.
>
>           postgres
>      /-----/  \-----\
>      |              |
>   plperl.so ---> Opcode.so
>              ^^
> This link doesn't happen.

    But  it does for PL/Tcl - at least under Linux-ELF. (C = Call
    to, L = Location of functions code segment):

      +-------------------------+
      |       postgres          |
      +-------------------------+
                   |
                   | dynamic load
                   |
                   v
      +---------------------------+          +---------------------------+
      | pltcl.so                  |--------->| libtcl8.0.so              |
      |                           |  auto-   |                           |
      | C Tcl_CreateInterp()      |  dynamic | L Tcl_CreateInterp()      |
      | C Tcl_CreateCommand()     |  load    | L Tcl_CreateCommand()     |
      | L static pltcl_SPI_exec() |          | C pltcl_SPI_exec()        |
      +---------------------------+          +---------------------------+

    After loading of pltcl.so,  it  calls  Tcl_CreateInterp()  to
    build  a  Tcl interpreter, and then calls Tcl_CreateCommand()
    to tell that interpreter the address of one  of  it's  hidden
    (static)  functions  plus a name for it from the script side.
    The interpreter just remembers  this  in  it's  command  hash
    table,   and  if  that  keyword  occurs  when  it  expects  a
    command/procedure  name,  just  calls  it  via  the  function
    pointer.

    There  is  no  -ltcl8.0  switch in the link step of postgres.
    The fact that pltcl.so needs something out of libtcl8.0.so is
    told when linking pltcl.so:

        gcc -shared -o pltcl.so pltcl.o -L/usr/local/lib -ltcl8.0

    That results in this:

    [pgsql@hot] ~ > ldd bin/postgres
            libdl.so.1 => /lib/libdl.so.1 (0x4000a000)
            libm.so.5 => /lib/libm.so.5 (0x4000d000)
            libtermcap.so.2 => /usr/lib/libtermcap.so.2 (0x40016000)
            libncurses.so.3.0 => /lib/libncurses.so.3.0 (0x4001a000)
            libc.so.5 => /lib/libc.so.5 (0x4005b000)
    [pgsql@hot] ~ > ldd lib/pltcl.so
            ./lib/pltcl.so => ./lib/pltcl.so (0x4000a000)
            libc.so.5 => /lib/libc.so.5 (0x40010000)
            libtcl8.0.so => /usr/local/lib/libtcl8.0.so (0x400cb000)

    As you see, there is no libtcl mentioned in  the  shared  lib
    dependencies  of  the  postgres  backend.  It's  the pltcl.so
    shared object that remembers this. And if you invoke "ldd  -r
    -d pltcl.so" it will print alot of unresolveable symbols, but
    most of them are backend symbols (the others  are  math  ones
    because  the  above  gcc -shared call is in fact incomplete -
    but since the backend is already linked  against  libm.so  it
    doesn't matter :-).

    So  if  I want to use My dynamically loadable package for Tcl
    from inside the PL/Tcl interpreter,  I  would  have  to  call
    My_Init()  from  pltcl.so  AND  add  My.so  to the linkage of
    pltcl.so. Calling My_Init()  causes  that  "pltcl.o"  has  an
    unresolved reference to symbol _My_Init. The linker find's it
    in My.so and saves this  info  in  pltcl.so  so  the  dynamic
    loader  can  (and  does)  resolve  it whenever something load
    pltcl.so.

    The important key is to reference at least one symbol in  the
    shared  lib you want to get automatically loaded. You can add
    as much link libs with -l as  you  want.  If  none  of  their
    symbols  is  needed, the linker will not save this dependency
    (because there is none) in the resulting .so.

    I'll give it a try and USE  some  binary  Tcl  packages  from
    inside.  Will tell ya soon.

> Getting those two to play together is more than I care to attempt.
> I am researching a fix now to let linux installations use dlopen
> if it is available.

    Don't think you need to.

> >     This is just the way I would do it for Tcl and I'll surely do
> >     it   someday.    I  would  like  to  have  a  second,  unsafe
> >     interpreter in the module.  That could then modify  files  or
> >     use  the  frontend  library to access a different database on
> >     another server. Needless to say that this then  would  be  an
> >     untrusted language, available only for db superusers.
> >
>
> Yes, I've been thinking about that as well. It would be nice to have
> permissions based on userid. Maybe the 'suid' stuff that is being
> discussed in another thread will gives us a mechanism.

    I  know,  I  know  -  and  I  know  how.  It  cannot work for
    "internal" language functions. But  for  anything  that  goes
    through  some loading (dynloader or PL call hander), the fmgr
    looks up pg_proc and put's  informations  into  the  FmgrInfo
    struct. Adding a setuid field to pg_proc and remembering that
    too wouldn't be too much and it then would know when  calling
    such  a  beast.  Fmgr then manages a current user stack which
    must be reset on a transaction abort. Anything that needs the
    current user simply looks at the toplevel stack entry.

    This   is   totally  transparent  then  for  all  non-builtin
    functions and all non-builtin triggers (where I don't know of
    one).

    Maybe  I  kept this far too long in mind. But I thought about
    some more complicated changes to the function call  interface
    for  a  while  that  would require touching several dozens of
    source files (single argument NULL identification,  returning
    tuples  and  tuple SET's).  Doing SETUID would have been some
    DONE WHILE AT IT. I really should  do  it  earlier  than  the
    SET's,  because they require subselecting RTE's (which it the
    third thread now - eh -  I better shut up).


Jan

--

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



Tue, 27 Jul 1999 01:01:44 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] plperl intial pass
Jan Wieck wrote:
> 
> Mark Hollomon wrote:
>
> > Jan Wieck wrote:
> > > 
> > >     A  dynamically  loadable  Tcl  module  contains  one  special
> > >     function  named  <libname>_Init()  where  first  character of
> > >     libname is capitalized.  On dynamic load,  this  function  is
> > >     called  with  the  invoking  interpreter  as  argument.  This
> > >     function then calls  Tcl_CreateCommand()  etc.  to  tell  Tcl
> >                            ^^^^^^^^^^^^^^^^^
> >
> > And here-in lies the problem. Tcl_CreateCommand is sitting, not
> > in the executable, but in the shared-lib with the function call
> > handler. dlopen(), by default will not link across shared-libs.
> >
> >           postgres
> >      /-----/  \-----\
> >      |              |
> >   plperl.so ---> Opcode.so
> >              ^^
> > This link doesn't happen.
> 
>     But  it does for PL/Tcl - at least under Linux-ELF. (C = Call
>     to, L = Location of functions code segment):
> 
>       +-------------------------+
>       |       postgres          |
>       +-------------------------+
>                    |
>                    | dynamic load
>                    |
>                    v
>       +---------------------------+          +---------------------------+
>       | pltcl.so                  |--------->| libtcl8.0.so              |
>       |                           |  auto-   |                           |
>       | C Tcl_CreateInterp()      |  dynamic | L Tcl_CreateInterp()      |
>       | C Tcl_CreateCommand()     |  load    | L Tcl_CreateCommand()     |
>       | L static pltcl_SPI_exec() |          | C pltcl_SPI_exec()        |
>       +---------------------------+          +---------------------------+
> 
>     After loading of pltcl.so,  it  calls  Tcl_CreateInterp()  to
>     build  a  Tcl interpreter, and then calls Tcl_CreateCommand()
>     to tell that interpreter the address of one  of  it's  hidden
>     (static)  functions  plus a name for it from the script side.
>     The interpreter just remembers  this  in  it's  command  hash
>     table,   and  if  that  keyword  occurs  when  it  expects  a
>     command/procedure  name,  just  calls  it  via  the  function
>     pointer.


AHHH, now I understand the difference. By default, the perl installation
does not create a shared library. It creates a static archive only.
And the three linux distros that I have experience with don't force
the creation of the shared lib. So, my situation is:

     postgres
       |
       |
    +----------------------+            +-----------------+
    |   plperl.so          |            |  Opcode.so      |
    |   +--------------+   |            |                 |
    |   | libperl.a    | <-+------------|                 |
    |   +--------------+   |            |                 |
    +----------------------+            +-----------------+

And it is THAT link that I cannot get to happen without the RTLD_GLOBAL
flag I mentioned.

Sorry for the confusion.

Hopefully you can help find a way out of this.

I had a patch to change the way dynloader worked on linuxelf,
but over night my disk crashed. brand new UDMA/66 drive. Grrrr.


-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Tue, 27 Jul 1999 08:54:47 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
dynloader and PLs [was: plperl intial pass]
Jan Wieck wrote:

> 

> Mark Hollomon wrote:

>

> > Yes, I've been thinking about that as well. It would be nice to have

> > permissions based on userid. Maybe the 'suid' stuff that is being

> > discussed in another thread will gives us a mechanism.

> 

>     I  know,  I  know  -  and  I  know  how.  It  cannot work for

>     "internal" language functions. But  for  anything  that  goes

>     through  some loading (dynloader or PL call hander), the fmgr

>     looks up pg_proc and put's  informations  into  the  FmgrInfo

>     struct. Adding a setuid field to pg_proc and remembering that

>     too wouldn't be too much and it then would know when  calling

>     such  a  beast.  Fmgr then manages a current user stack which

>     must be reset on a transaction abort. Anything that needs the

>     current user simply looks at the toplevel stack entry.



That would work.



> 

>     This   is   totally  transparent  then  for  all  non-builtin

>     functions and all non-builtin triggers (where I don't know of

>     one).

> 

>     Maybe  I  kept this far too long in mind. But I thought about

>     some more complicated changes to the function call  interface

>     for  a  while  that  would require touching several dozens of

>     source files (single argument NULL identification,  returning

>     tuples  and  tuple SET's).  Doing SETUID would have been some

>     DONE WHILE AT IT. I really should  do  it  earlier  than  the

>     SET's,  because they require subselecting RTE's (which it the

>     third thread now - eh -  I better shut up).



I've been looking at returning a tuple. It looked to me that the

executor would handle a returned tuple okay, it was just SETs that 
would cause problems. But I suspect I am wrong.



The best I could come up with for creating the tuple was using

heap_formtuple. But that requires a TupleDesc so I was going to

use heap_openr. But that needs the name of the relation which is

avaible from the Form_pg_data (?) structure for the return type,

which we already must get.


-- 



Mark Hollomon

mhh@nortelnetworks.com

ESN 451-9008 (302)454-9008

Tue, 27 Jul 1999 09:01:01 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
fmgr interface [was: plperl inital pass]
Mark Hollomon wrote:

> I've been looking at returning a tuple. It looked to me that the
>
> executor would handle a returned tuple okay, it was just SETs that
> would cause problems. But I suspect I am wrong.

    Functions  returning  SET's  allways  return SET's of tuples,
    never SET's of single values. And functions  returning  tuple
    (SET's)  have  a targetlist to specify which attribute of the
    returned tuple(s) is wanted. It is  the  processing  of  this
    funciton-call-targetlist   that's   actually  broken  in  the
    executor.

    But it's not worth fixing it without beeing able after to use
    more  than  one  attribute  of  the  returned  set.  And that
    requires the mentioned subselecting RTE. So  you  could  then
    say things like:

        SELECT X.a, X.c FROM mysetfunc('Mark') X;

    The next problem in returning SET's is, that PostgreSQL isn't
    a state machine - it  is  stack  oriented.  The  way  it  was
    supposed to work with SQL language functions was this:

    1.  The  last  query in an SQL function returning a tuple SET
        is allways a SELECT.

    2.  When the FUNC node is first  hit  during  execution,  the
        function  is  called.   Then the FUNC node is modified by
        the executor and references the  execution  tree  of  the
        last command in the function.

    3.  Subsequent  function  calls  don't  invoke  the  function
        again, instead functions last commands execution tree  is
        asked for the next tuple.

    This  mechanism  could  also  work  for  PL  functions.  A PL
    function returning a  SET  creates  a  temp  table.  At  each
    occurence of

        RETURN mytup AND RESUME;

    it  adds  the  tuple  to the temp table. If it finally really
    returns, it hands back an execution plan for a

        SELECT * FROM <my_invocations_temp_table>;

    Then again, the problem of using multiple attributes  of  the
    returned set remains.


> The best I could come up with for creating the tuple was using
>
> heap_formtuple. But that requires a TupleDesc so I was going to
>
> use heap_openr. But that needs the name of the relation which is
>
> avaible from the Form_pg_data (?) structure for the return type,
>
> which we already must get.

    Of   course,   the   PL   function  must  create  tuples  via
    heap_formtuple().  Thus, we need a pg_class entry (etc.)  for
    it. The PL handler knows the return type of the function it's
    handling from pg_proc. The corresponding pg_type entry has  a
    non-zero  typrelid indicating that it's a tuple type.  Simply
    use heap_open() with that typrelid and you'll get it.

    I'd like to add a new type of relation when we go for  return
    SET's.

        CREATE STRUCTURE structname (attname type [, ...]);

    It  just  causes  another pg_class entry, but these relations
    aren't  accessible  by  normal  means  and  do  not  have  an
    underlying  file.  Don't  know  if it's valid SQL syntax, but
    what else could tell the parser what type of a  tuple  a  SET
    function  will  return  if  it's  not  an  existing  relation
    structure?


Jan

--

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



Tue, 27 Jul 1999 16:39:27 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: fmgr interface [was: plperl inital pass]
Mark Hollomon wrote:

> AHHH, now I understand the difference. By default, the perl installation
> does not create a shared library. It creates a static archive only.
> And the three linux distros that I have experience with don't force
> the creation of the shared lib. So, my situation is:
>
>      postgres
>        |
>        |
>     +----------------------+            +-----------------+
>     |   plperl.so          |            |  Opcode.so      |
>     |   +--------------+   |            |                 |
>     |   | libperl.a    | <-+------------|                 |
>     |   +--------------+   |            |                 |
>     +----------------------+            +-----------------+
>
> And it is THAT link that I cannot get to happen without the RTLD_GLOBAL
> flag I mentioned.

    Yes - we need to understand the differences. After looking at
    some perl manpages (perlxs, perlembed, perlmodlib  etc.)  and
    consulting Opcode.pm I see the problems clearer now.

    Under Tcl, you can simply type "load <shared-object>" to load
    a .so and cause a call to it's ..._Init() function.  Whatever
    comes  there, the .so's ..._Init() function will tell it. And
    since every C function that should be callable  from  Tcl  is
    given  to  the  interpreter as a function pointer from within
    the ..._Init(), nothing except the ..._Init() function itself
    must  be  really resolved. In fact, the functions called from
    Tcl can be declared static inside the shared  object  (what's
    true in pltcl) so there are no symbols to resolve.

    A   safe  Tcl  interpreter  has  no  load  command.  But  the
    controlling C  application  can  call  the  .so's  ..._Init()
    function  directly to simulate the "load" (well, it should be
    the ..._SafeInit(), but that's  another  story).  Thus,  a  C
    application  creating a safe interpreter can load modules for
    it even if the interpreter itself can't.

    Under Perl, a  package  using  a  shared  object  is  allways
    surrounded  by some .pm which tells to lookup symbols via the
    dynamic loader (if I understand XSUB's right). So it's  still
    a  type  of  a  script  that  controls  the  Perl->C-function
    bindings, not the shared object itself.

    The  detail  I  don't  understand  is  what   breaks   Perl's
    dynaloader if you use it from inside of plperl.so. Since Perl
    isn't built shared, the entire  libperl.a  should  be  linked
    static  into  plperl.so.  What's the exact error message when
    you try to USE Opcode?

>
> Sorry for the confusion.
>
> Hopefully you can help find a way out of this.
>
> I had a patch to change the way dynloader worked on linuxelf,

    I don't think you should change the entire dynamic loader  of
    PostgreSQL  for  it.  This  could  be  a can of worms and you
    should be happy that these problems showed up already on your
    development  platform.  I don't expect that you're willing to
    fix the dynamic loading under  AIX,  HP-UX  and  Solaris  too
    (maybe   you   can't   because   the   lack   of  appropriate
    environment).

> but over night my disk crashed. brand new UDMA/66 drive. Grrrr.

    Ech


Jan

--

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



Tue, 27 Jul 1999 19:05:28 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: dynloader and PLs [was: plperl intial pass]
Jan Wieck wrote:
> 
> Mark Hollomon wrote:
> 
> > I had a patch to change the way dynloader worked on linuxelf,
> 
>     I don't think you should change the entire dynamic loader  of
>     PostgreSQL  for  it.  This  could  be  a can of worms and you
>     should be happy that these problems showed up already on your
>     development  platform.  I don't expect that you're willing to
>     fix the dynamic loading under  AIX,  HP-UX  and  Solaris  too
>     (maybe   you   can't   because   the   lack   of  appropriate
>     environment).
> 

The problem is that perl and postgres disagree as how to do
the dynamic loading. postgres (on linux) _Always_ use aout
style dynamic loading. Perl checks to see if the system is ELF
and use dlopen if it is. On my ELF system then, postgres is
loading plperl.so with dl_open (?). Then perl is loading
Opcode.so using dlopen. The problem seems to be that the symbols
from libperl.a (in plperl.so) are not available for resolving
missing symbols in Opcode.so. The error message basically mentions
every perl symbol as 'unresolved'.

I noticed in another thread that D'Arcy is strugling with a similar
problem in NetBSD.

On my system, once I got postgres and perl to agree on how to do
dynamic loading, I got XS stuff working. The code is (mostly)
already in plperl.c, but ifdef'ed out.


-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Tue, 27 Jul 1999 14:06:25 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: dynloader and PLs [was: plperl intial pass]