DBD::PgSPI 0.02

Hello,

A short note that I've updated DBD::PgSPI version 0.02 to CPAN.

There are no new features - but the code now expects (and works with)
reasonably decent versions of perl (5.8.x) and pgsql (8.x).

No warranty is given, this code compiles and 'scratches my itch'. If it
happens to scratch yours, more the merrier.

-alex

On Mon, Dec 06, 2004 at 12:27:18AM -0500, alex(at)pilosoft(dot)com wrote:

> A short note that I've updated DBD::PgSPI version 0.02 to CPAN.
>
> There are no new features - but the code now expects (and works with)
> reasonably decent versions of perl (5.8.x) and pgsql (8.x).

Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and
Solaris 9, I had to make a couple of changes to get DBD::PgSPI to
build:

1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise
the build fails with:

In file included from PgSPI.xs:14:
PgSPI.h:16:22: postgres.h: No such file or directory
PgSPI.h:17:21: funcapi.h: No such file or directory
PgSPI.h:18:26: executor/spi.h: No such file or directory

2. Remove or comment out #include "ppport.h" from PgSPI.h. Neither
of my systems have this file and the module builds without it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, 5 Dec 2004, Michael Fuhr wrote:

> Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and
> Solaris 9, I had to make a couple of changes to get DBD::PgSPI to
> build:
>
> 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise
> the build fails with:
You should point POSTGRES_HOME to "src" directory of your pgsql tree.

>
> In file included from PgSPI.xs:14:
> PgSPI.h:16:22: postgres.h: No such file or directory
> PgSPI.h:17:21: funcapi.h: No such file or directory
> PgSPI.h:18:26: executor/spi.h: No such file or directory
>
> 2. Remove or comment out #include "ppport.h" from PgSPI.h. Neither of
> my systems have this file and the module builds without it.
Strange that 'make tardist' didn't include it. I'm not sure if its even
required or not to have backward compatibility (to perl 5.4 for example)
or not. Or whether I even care about backward compatibility. I'll remove
it in next release, I suppose.

-alex

On Mon, Dec 06, 2004 at 01:38:27AM -0500, alex(at)pilosoft(dot)com wrote:
> On Sun, 5 Dec 2004, Michael Fuhr wrote:
>
> > Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and
> > Solaris 9, I had to make a couple of changes to get DBD::PgSPI to
> > build:
> >
> > 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise
> > the build fails with:
>
> You should point POSTGRES_HOME to "src" directory of your pgsql tree.

Why should the module need the PostgreSQL source code? It builds
fine using the headers under the PostgreSQL install directory (e.g.,
/usr/local/pgsql/include), at least with 8.0.0rc1.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sun, 5 Dec 2004, Michael Fuhr wrote:

> On Mon, Dec 06, 2004 at 01:38:27AM -0500, alex(at)pilosoft(dot)com wrote:
> > On Sun, 5 Dec 2004, Michael Fuhr wrote:
> >
> > > Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and
> > > Solaris 9, I had to make a couple of changes to get DBD::PgSPI to
> > > build:
> > >
> > > 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise
> > > the build fails with:
> >
> > You should point POSTGRES_HOME to "src" directory of your pgsql tree.
>
> Why should the module need the PostgreSQL source code? It builds
> fine using the headers under the PostgreSQL install directory (e.g.,
> /usr/local/pgsql/include), at least with 8.0.0rc1.
I don't think the headers I need (postgres.h funcapi.h) are installed in a
normal install. At least they aren't on my machine. Intrinsically, this
module needs to know a little bit more about pgsql internals (such as
TupleDesc definition) than just something that uses libpq...

If I'm wrong, please correct me.

-alex

On Sun, 5 Dec 2004, Michael Fuhr wrote:

> 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise the
> build fails with:
On second thought: Apparently that if I do 'make install-all-headers' I
would get the files I need in include/server. I didn't know this - make
install won't install by default. I'll add proper path to makefile for
next release (sooner than 3 years this time ;)

-alex

On Mon, Dec 06, 2004 at 02:10:07AM -0500, alex(at)pilosoft(dot)com wrote:
> On Sun, 5 Dec 2004, Michael Fuhr wrote:

> > Why should the module need the PostgreSQL source code? It builds
> > fine using the headers under the PostgreSQL install directory (e.g.,
> > /usr/local/pgsql/include), at least with 8.0.0rc1.
>
> I don't think the headers I need (postgres.h funcapi.h) are installed in a
> normal install. At least they aren't on my machine. Intrinsically, this
> module needs to know a little bit more about pgsql internals (such as
> TupleDesc definition) than just something that uses libpq...
>
> If I'm wrong, please correct me.

Those are probably the server-side development headers that 7.x
and early 8.0 betas didn't install by default -- you had to run
"gmake install-all-headers". In 8.0 all headers are now installed
with "gmake install".

People installing PostgreSQL using pre-built packages will probably
have to install an additional package with a name like postgresql-devel
to get the additional headers. That's normal for doing PostgreSQL
server-side development.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Dec 06, 2004 at 02:16:19AM -0500, alex(at)pilosoft(dot)com wrote:
> On Sun, 5 Dec 2004, Michael Fuhr wrote:
>
> > 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise the
> > build fails with:
>
> On second thought: Apparently that if I do 'make install-all-headers' I
> would get the files I need in include/server. I didn't know this - make
> install won't install by default. I'll add proper path to makefile for
> next release (sooner than 3 years this time ;)

I don't know if you want to incorporate this into your Makefile.PL,
but you can get the header and library paths with:

pg_config --includedir
pg_config --includedir-server
pg_config --libdir

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, 6 Dec 2004 00:27:18 -0500 (EST), alex(at)pilosoft(dot)com
<alex(at)pilosoft(dot)com> wrote:
> Hello,
>
> A short note that I've updated DBD::PgSPI version 0.02 to CPAN.
>
> There are no new features - but the code now expects (and works with)
> reasonably decent versions of perl (5.8.x) and pgsql (8.x).

Just so that you have some info, I've been using DBD::PgSPI with Pg
8.0 since beta 1. The only restriction I've run into with the old
code is that it doesn't like the DBD 'do' method. I have to use
execute/fetchX or selectX, but other than that it seems to work. I'll
be grabbing the update to test soon.

>
> No warranty is given, this code compiles and 'scratches my itch'. If it
> happens to scratch yours, more the merrier.
>

Thanks for scratching your itch! I'm sure you're merrie than you know. :)

> -alex
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

On Mon, 6 Dec 2004, Mike Rylander wrote:

> Just so that you have some info, I've been using DBD::PgSPI with Pg 8.0
> since beta 1. The only restriction I've run into with the old code is
> that it doesn't like the DBD 'do' method. I have to use execute/fetchX
> or selectX, but other than that it seems to work. I'll be grabbing the
> update to test soon.
'do' seems to work for me. What happens for you?

I put a version of code with a bit more fixes from comments onlist to
www.pilosoft.com/PgSPI/DBD-PgSPI-0.03pre.tar.gz

Please download and try it.

-alex

On Mon, 6 Dec 2004, Mike Rylander wrote:

> With v. 0.01 the statement just doesn't seem to execute. I know that's
> odd, because it's supposed to be doing a prepare/execute internally, but
> nothing happens.
Wierd - the testsuite (make test) has some 'dos' in the code and it works
(and it checks the result of inserts that are done with do).
>
> I'll grab it now, but I'm not sure when I'll have time to test it. I'm
> mired in Unicode work right now... I'll get back to you as soon as I've
> had a chance to work with it, though.
Yeah - I'm not doing any utf translation in pgspi - this might become
necessary for you...
>
> Again, thanks for all your work on PgSPI. I'll be moving all my plperlu
> functions out to a module and using PgSPI to allow me to run the code
> from my middleware layer as well as in-DB. That way I only need to
> write the logic once.
Yep, that's kind of was the point, making postgresql itself a middleware
server. Make perl stored procedures take serialized objects and return
serialized objects back.

-alex

On Mon, 6 Dec 2004 08:17:29 -0500 (EST), alex(at)pilosoft(dot)com
<alex(at)pilosoft(dot)com> wrote:
> On Mon, 6 Dec 2004, Mike Rylander wrote:
>
> > Just so that you have some info, I've been using DBD::PgSPI with Pg 8.0
> > since beta 1. The only restriction I've run into with the old code is
> > that it doesn't like the DBD 'do' method. I have to use execute/fetchX
> > or selectX, but other than that it seems to work. I'll be grabbing the
> > update to test soon.
> 'do' seems to work for me. What happens for you?

With v. 0.01 the statement just doesn't seem to execute. I know
that's odd, because it's supposed to be doing a prepare/execute
internally, but nothing happens.

>
> I put a version of code with a bit more fixes from comments onlist to
> www.pilosoft.com/PgSPI/DBD-PgSPI-0.03pre.tar.gz
>
> Please download and try it.
>

I'll grab it now, but I'm not sure when I'll have time to test it.
I'm mired in Unicode work right now... I'll get back to you as soon
as I've had a chance to work with it, though.

> -alex
>
>

Again, thanks for all your work on PgSPI. I'll be moving all my
plperlu functions out to a module and using PgSPI to allow me to run
the code from my middleware layer as well as in-DB. That way I only
need to write the logic once.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org

On Mon, Dec 06, 2004 at 08:17:29AM -0500, alex(at)pilosoft(dot)com wrote:

> I put a version of code with a bit more fixes from comments onlist to
> www.pilosoft.com/PgSPI/DBD-PgSPI-0.03pre.tar.gz

After correcting the path (no directory) I downloaded this and it
built without changes on FreeBSD 4.10-STABLE and Solaris 9, both
running PostgreSQL 8.0.0rc1 and Perl 5.8.6. However, something
changed since 0.02 that broke queries on FreeBSD (but not on Solaris).
Here's an example:

CREATE FUNCTION test() RETURNS INTEGER AS $$
use DBD::PgSPI;
elog INFO, "DBD::PgSPI $DBD::PgSPI::VERSION";
my @row = $pg_dbh->selectrow_array("SELECT 12345");
return $row[0];
$$ LANGUAGE plperlu;

If I install DBD::PgSPI 0.02 I get this:

SELECT test();
INFO: DBD::PgSPI 0.02
test
-------
12345
(1 row)

If I disconnect, install DBD::PgSPI 0.03pre, then reconnect and run
the same query, I get this:

SELECT test();
INFO: DBD::PgSPI 0.03pre
test
------

(1 row)

Any idea what might have changed between 0.02 and 0.03pre that would
affect some platforms but not others?

A few other comments:

The README file says that "plperl is NOT part of latest (7.1)
distribution...". The latest release is now 7.4.6 with 8.0.0
coming soon, and both include plperl.

The TODO section in the README file has items for "Support for
returning rows and sets of rows" and "Support for writing trigger
handlers." In 8.0 PL/Perl has these capabilities.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

BTW, I would like to get these capabilities into core plperl.

There are some obstacles to overcome. For example:

. not every perl installation has DBI
. how to turn it on for trusted plperl
. DBD::PgSPI is covered by GPL, which means it can't be used in the core
distribution of postgres - we'd have to reinvent it in a clean room fashion.

cheers

andrew

Michael Fuhr wrote:

>On Mon, Dec 06, 2004 at 08:17:29AM -0500, alex(at)pilosoft(dot)com wrote:
>
>
>
>>I put a version of code with a bit more fixes from comments onlist to
>>www.pilosoft.com/PgSPI/DBD-PgSPI-0.03pre.tar.gz
>>
>>
>
>After correcting the path (no directory) I downloaded this and it
>built without changes on FreeBSD 4.10-STABLE and Solaris 9, both
>running PostgreSQL 8.0.0rc1 and Perl 5.8.6. However, something
>changed since 0.02 that broke queries on FreeBSD (but not on Solaris).
>Here's an example:
>
>CREATE FUNCTION test() RETURNS INTEGER AS $$
>use DBD::PgSPI;
>elog INFO, "DBD::PgSPI $DBD::PgSPI::VERSION";
>my @row = $pg_dbh->selectrow_array("SELECT 12345");
>return $row[0];
>$$ LANGUAGE plperlu;
>
>If I install DBD::PgSPI 0.02 I get this:
>
>SELECT test();
>INFO: DBD::PgSPI 0.02
> test
>-------
> 12345
>(1 row)
>
>If I disconnect, install DBD::PgSPI 0.03pre, then reconnect and run
>the same query, I get this:
>
>SELECT test();
>INFO: DBD::PgSPI 0.03pre
> test
>------
>
>(1 row)
>
>Any idea what might have changed between 0.02 and 0.03pre that would
>affect some platforms but not others?
>
>A few other comments:
>
>The README file says that "plperl is NOT part of latest (7.1)
>distribution...". The latest release is now 7.4.6 with 8.0.0
>coming soon, and both include plperl.
>
>The TODO section in the README file has items for "Support for
>returning rows and sets of rows" and "Support for writing trigger
>handlers." In 8.0 PL/Perl has these capabilities.
>
>
>

On Mon, 6 Dec 2004, Andrew Dunstan wrote:

> BTW, I would like to get these capabilities into core plperl.
There is already spi_exec_query in pl/perl for quick and dirty DB access
for this purpose, no?

> There are some obstacles to overcome. For example:
>
> . not every perl installation has DBI
Sure - I suppose if you don't have DBI, you would use spi_exec_query, no?

> . how to turn it on for trusted plperl
Eh, you don't turn it on. You install the package and it works ;)

> . DBD::PgSPI is covered by GPL, which means it can't be used in the core
> distribution of postgres - we'd have to reinvent it in a clean room fashion.
Actually, its both GPL and Artistic license - identical to DBD::Pg (where
most of the code is taken from).

I don't think this needs to be in core distribution - much like DBD::Pg
doesn't need to be there either...

-alex

On Mon, Dec 06, 2004 at 11:44:20AM -0700, Michael Fuhr wrote:
>
> After correcting the path (no directory) I downloaded this and it
> built without changes on FreeBSD 4.10-STABLE and Solaris 9, both
> running PostgreSQL 8.0.0rc1 and Perl 5.8.6. However, something
> changed since 0.02 that broke queries on FreeBSD (but not on Solaris).

Never mind -- operator error. The FreeBSD box has multiple versions
of PostgreSQL installed and I was getting the wrong pg_config when
building DBD::PgSPI for 8.0 (that's what I get for mentioning pg_config).
Sorry for the bogus report.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, 6 Dec 2004, Tom Lane wrote:

> alex(at)pilosoft(dot)com writes:
> > On Mon, 6 Dec 2004, Andrew Dunstan wrote:
> >> . how to turn it on for trusted plperl
>
> > Eh, you don't turn it on. You install the package and it works ;)
>
> Really? If the plperl Safe opmask allows that, we've got some problems.
Errr my bad. I keep confusing trusted/untrusted. It does not allow it, nor
should it.

The purpose of PgSPI is to write 'middleware' solutions in perl - the idea
is that you can take a piece of existing client-side code and make a
server-side stored procedure out of it in a minute without any changes to
the code.

For quick access from trusted code, spi_exec should just do fine.

-alex

alex(at)pilosoft(dot)com writes:
> On Mon, 6 Dec 2004, Andrew Dunstan wrote:
>> . how to turn it on for trusted plperl

> Eh, you don't turn it on. You install the package and it works ;)

Really? If the plperl Safe opmask allows that, we've got some problems.

regards, tom lane

On Mon, 6 Dec 2004, Andrew Dunstan wrote:

> I disagree. The crucial difference is that DBD::Pg is a client side
> library and plperl is not.
>
> I would like all perl programmers to be able to use the same (or
> similar) idioms on both the client side and the server side. (Just as
> one can use JDBC idioms in PL/Java, iirc). So I want a DBI handle to be
> available in core plperl if possible.
Just like perl programmers need to download DBD::Pg to deal with Pg on
client side, I don't see what's so wrong with them having to download
DBD::PgSPI to deal with it on server side.

Besides the fact is that they have to download DBI to get *anywhere* in
the first place ;0

-alex

alex(at)pilosoft(dot)com wrote:

>>. DBD::PgSPI is covered by GPL, which means it can't be used in the core
>>distribution of postgres - we'd have to reinvent it in a clean room fashion.
>>
>>
>Actually, its both GPL and Artistic license - identical to DBD::Pg (where
>most of the code is taken from).
>
>I don't think this needs to be in core distribution - much like DBD::Pg
>doesn't need to be there either...
>
>
>
>

I disagree. The crucial difference is that DBD::Pg is a client side
library and plperl is not.

I would like all perl programmers to be able to use the same (or
similar) idioms on both the client side and the server side. (Just as
one can use JDBC idioms in PL/Java, iirc). So I want a DBI handle to be
available in core plperl if possible.

cheers

andrew

On Mon, 6 Dec 2004, Michael Fuhr wrote:

> On Mon, Dec 06, 2004 at 02:34:33PM -0500, alex(at)pilosoft(dot)com wrote:
> >
> > For quick access from trusted code, spi_exec should just do fine.
>
> BTW, does stock PL/Perl have functions for escaping identifiers,
> strings, and binary strings?
non-DBI? no.

DBI? yes, $pg_dbh->quote('foo')

On Mon, Dec 06, 2004 at 02:34:33PM -0500, alex(at)pilosoft(dot)com wrote:
>
> For quick access from trusted code, spi_exec should just do fine.

BTW, does stock PL/Perl have functions for escaping identifiers,
strings, and binary strings?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Mon, Dec 06, 2004 at 03:02:45PM -0500, alex(at)pilosoft(dot)com wrote:
> On Mon, 6 Dec 2004, Michael Fuhr wrote:
>
> > On Mon, Dec 06, 2004 at 02:34:33PM -0500, alex(at)pilosoft(dot)com wrote:
> > >
> > > For quick access from trusted code, spi_exec should just do fine.
> >
> > BTW, does stock PL/Perl have functions for escaping identifiers,
> > strings, and binary strings?
>
> non-DBI? no.
>
> DBI? yes, $pg_dbh->quote('foo')

Yeah, I know about DBI, but since we currently can't use it in
trusted code I was wondering what we *could* use. With DBI I'd be
using placeholders wherever possible, but unless I've missed something
spi_exec_query() requires values to be interpolated into the query
string. Danger, danger.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr wrote:

>>DBI? yes, $pg_dbh->quote('foo')
>>
>>
>
>Yeah, I know about DBI, but since we currently can't use it in
>trusted code I was wondering what we *could* use. With DBI I'd be
>using placeholders wherever possible, but unless I've missed something
>spi_exec_query() requires values to be interpolated into the query
>string. Danger, danger.
>
>

One of the relatively unnoticed features of 8.0's plperl is %_SHARED.
This is a hash available to all trusted and untrusted code, and can be
used to store arbitrary objects. That includes references to
subroutines. So you could have an init function that you call once per
session that sets up some utility functions for you and stores them
there. Writing a quote function shuld not be too hard. (Some
automatically called init code is another item on the plperl agenda.)

moderately tested example:

-- set up the quote function
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void LANGUAGE plperl AS $$

$_SHARED{myquote} = sub

{

my $arg = shift;

$arg =~ s/(['\\])/\\$1/g;

return "'$arg'";

};

$$;

SELECT myfuncs();

-- set up a function that uses the quote function
CREATE OR REPLACE FUNCTION use_quote(text) RETURNS text LANGUAGE plperl AS $$

my $text_to_quote = shift;
my $qfunc = $_SHARED{myquote};
return &$qfunc($text_to_quote);

$$;

SELECT use_quote($$bl\ur'fl$$);

cheers

andrew

alex(at)pilosoft(dot)com writes:
> On Mon, 6 Dec 2004, Andrew Dunstan wrote:
>>> . how to turn it on for trusted plperl
>> [ snip ]

> Errr my bad. I keep confusing trusted/untrusted. It does not allow it, nor
> should it.

> The purpose of PgSPI is to write 'middleware' solutions in perl - the idea
> is that you can take a piece of existing client-side code and make a
> server-side stored procedure out of it in a minute without any changes to
> the code.

Sure. But you don't run your middleware as root (I hope ;-)) and you
shouldn't run it in untrusted server-side languages either. I agree
with Andrew that it's important to figure out how to make DBI usable
in trusted plperl. Obviously this isn't happening in time for 8.0,
but it deserves a place on the TODO list.

regards, tom lane

On Mon, 6 Dec 2004, Tom Lane wrote:

> Sure. But you don't run your middleware as root (I hope ;-)) and you
> shouldn't run it in untrusted server-side languages either. I agree
Actually - I don't practically care, and in fact I'm doing things that are
unsafe...But, I agree, others may think differently ;)

> with Andrew that it's important to figure out how to make DBI usable
> in trusted plperl. Obviously this isn't happening in time for 8.0,
> but it deserves a place on the TODO list.
It's interesting but its probably a untrivial effort to make DBI itself
Safe-safe. Probably it would require starting with DBI::PurePerl (non-XS
version) and adding a mode that would disable all unSafe activity (such as
file operations etc etc)...

-alex