> A bit of context here. The perl DBD::Pg developers are trying to
figure
> out
> how to implement prepared queries sanely. As it stands now they're
> basically
> writing off both binary prepared queries and SQL based prepared
queries as
> basically useless. It would be a shame to have a brand new binary
protocol
> but
> find it ignored by driver writers.
> 
> The problem is that you want to be able to do
> 
>  $sth = $dbh->prepare("SELECT col_a FROM tab WHERE col_b = ?");
>  $sth->execute(1);
>  ...
> 
> And not have to jump through hoops binding parameters to types and so
on.
> 

suggestion: default to text type ('character varying') and overload your
prepare method to allow a vector of types for special cases.  It follows
that if you don't know what type you are dealing with than it gets dealt
with as a string.

Question: what is the relevance of the binary protocol, are you trying
to send/fetch binary data via the command interface?

Merlin


Wed, 15 Sep 2004 08:48:58 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: libpq and prepared statements progress for 8.0
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Question: what is the relevance of the binary protocol, are you trying
> to send/fetch binary data via the command interface?

My understanding of the original post is that DBD::Pg is sitting atop
libpq and wants to keep doing so.  So they're going to need some
improvements to libpq to get at Parse-into-a-named-statement and
Describe Statement.  This is one of the things that didn't get done in
the 7.4 cycle, and no one seems to have got round to it later either.
But it's clearly a deficiency of libpq.

			regards, tom lane

Wed, 15 Sep 2004 10:56:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: libpq and prepared statements progress for 8.0 

Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> > Question: what is the relevance of the binary protocol, are you trying
> > to send/fetch binary data via the command interface?
> 
> My understanding of the original post is that DBD::Pg is sitting atop
> libpq and wants to keep doing so.  So they're going to need some
> improvements to libpq to get at Parse-into-a-named-statement and
> Describe Statement.  This is one of the things that didn't get done in
> the 7.4 cycle, and no one seems to have got round to it later either.
> But it's clearly a deficiency of libpq.

Well even without parse-into-a-named-statement they could be using PQexecParam
for now. I'm talking with them trying to straighten this out.

Is there anything technically hard in adding this functionality to libpq? It
looks like it's just mechanically adding more entry points to existing code.
Were you leaving this as a honey pot hoping it would attract new programmers?
I'm looking at doing it now.

The describe statement part could be much trickier but DBD::Pg doesn't really
need that for basic functionality. It would be a useful feature for later
though.


I do wonder whether DBD::Pg is really best off using libpq. From what I'm
reading now it seems the "read every record before returning" behaviour is
rooted in the libpq interface. Ideally a program should be able to stream
results and process them as they arrive. It looks like PQgetResult might be
relevant but the documentation isn't clear whether each result returned is for
an entire query in the original statement or if they can be partial result
sets.

-- 
greg


15 Sep 2004 13:40:30 -0400
Greg Stark <gsstark@mit.edu>
Re: libpq and prepared statements progress for 8.0
Greg Stark <gsstark@mit.edu> writes:
> Is there anything technically hard in adding this functionality to libpq? It
> looks like it's just mechanically adding more entry points to existing code.

Well, (a) I ran out of time, and (b) I wasn't sure what the most
convenient API would be.  Should we create something that packages
together a Parse and a Describe Statement, or expose those as
separate things?

There's surely no technical difficulty once you've got some consensus
on what the API should look like.

> The describe statement part could be much trickier but DBD::Pg doesn't really
> need that for basic functionality.

Doesn't it?  I thought the problem was that they couldn't find out what
datatypes the parameters got resolved as.  That may not be important
if they are happy with always shipping text strings, but if they want to
move over to using binary transmission of parameter values then they
gotta know the parameter types.

> I do wonder whether DBD::Pg is really best off using libpq.

I was wondering that myself.  Would they be able to implement a
pure-Perl driver if they ginned up their own protocol code?
It'd be a lot of work, of course, so I can understand that they
might not feel it's worth the trouble.

> From what I'm reading now it seems the "read every record before
> returning" behaviour is rooted in the libpq interface.

Right.  Again that's probably something that could be handled by
exposing more/different API, but no one has stepped up to design it.

			regards, tom lane

Wed, 15 Sep 2004 13:50:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: libpq and prepared statements progress for 8.0 
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > Is there anything technically hard in adding this functionality to libpq? It
> > looks like it's just mechanically adding more entry points to existing code.
> 
> Well, (a) I ran out of time, and (b) I wasn't sure what the most
> convenient API would be.  Should we create something that packages
> together a Parse and a Describe Statement, or expose those as
> separate things?

I don't know either. For my purposes it seems like a waste to be handling this
if I don't need it. On the other hand another round trip when it is needed
would be poor. Does the protocol provide it immediately or does it require
another message to the server?

> > The describe statement part could be much trickier but DBD::Pg doesn't really
> > need that for basic functionality.
> 
> Doesn't it?  I thought the problem was that they couldn't find out what
> datatypes the parameters got resolved as.  That may not be important
> if they are happy with always shipping text strings, but if they want to
> move over to using binary transmission of parameter values then they
> gotta know the parameter types.

Well personally I'm happy always shipping text strings. I'm sure someone else
will have different opinions but I don't really see why it would be any faster
to marshal data into Postgres's preferred binary representation than it would
to marshal it into a string. Neither are going to match Perl's internal
representation anyways.

I'm assuming the data type is always known to the programmer anyways and he
can ensure the data is provided in the appropriate Perl representation the
driver expects.

It could be useful for fancier situations like marshalling a timestamp from an
ambiguous perl datastructure that could represent an integer or integer array
into a Postgres string or binary representation of a timestamp.

Or perhaps for things like GUI tools that will display a user dialog box for
prompting for parameters of the appropriate type.

But these seem like refinements. The basic functionality is to offer the
equivalent functionality to what exists already, where the provided parameters
are simply interpolated into the query string.

> > I do wonder whether DBD::Pg is really best off using libpq.
> 
> I was wondering that myself.  Would they be able to implement a
> pure-Perl driver if they ginned up their own protocol code?
> It'd be a lot of work, of course, so I can understand that they
> might not feel it's worth the trouble.

Well a pure-Perl driver or a driver written in C with perl bindings wouldn't
really be an unreasonable amount of work I don't think. What I'm worried about
is whether it's the right strategy.

I was pretty shocked when I heard that JDBC implements the low level protocol
itself. It seems like a dead-end strategy to me. Any new protocol innovations
need to be implemented in every driver. Every implementation gets the chance
to reimplement the same bugs and same inefficiencies over and over again.

I had thought it was a better idea to have a library that handled the low
level protocol details. It should provide a 1-1 mapping for everything you can
do with the protocol. But at least that way you're guaranteed to never be
sending garbage down the wire getting the state machine out of sync with the
server.

> > From what I'm reading now it seems the "read every record before
> > returning" behaviour is rooted in the libpq interface.
> 
> Right.  Again that's probably something that could be handled by
> exposing more/different API, but no one has stepped up to design it.

It sort of seems to me that libpq's problem is trying to do too much. It tries
to be an abstract easy-to-use API for C programmers. But there's a need for a
low level API that just handles the protocol syntax and state machine and not
much more.

-- 
greg


15 Sep 2004 14:47:45 -0400
Greg Stark <gsstark@mit.edu>
Re: libpq and prepared statements progress for 8.0
Greg Stark wrote:

> I was pretty shocked when I heard that JDBC implements the low level protocol
> itself. It seems like a dead-end strategy to me. Any new protocol innovations
> need to be implemented in every driver. Every implementation gets the chance
> to reimplement the same bugs and same inefficiencies over and over again.

There *are* benefits to implementing the protocol directly. First on my 
personal list is that our Java application would not be able to use 
postgresql at all if the driver required JNI/libpq.

There are also some things in the JDBC API that seem hard to map to the 
current libpq API, e.g. streaming parameter data from a Java stream 
without taking an intermediate copy.

The protocol implementation is not really all that complex. The 
implementation for both V2 and V3 weighs in at ~6k lines of Java out of 
~38k total, and much of that is connection-state juggling that we'd have 
to do anyway if using libpq (working out when to send BEGIN, breaking up 
multiple-statement queries into individual statements and matching the 
results up, managing portal state, etc).

-O

Thu, 16 Sep 2004 09:21:02 +1200
Oliver Jowett <oliver@opencloud.com>
Re: libpq and prepared statements progress for 8.0

Oliver Jowett <oliver@opencloud.com> writes:

> Greg Stark wrote:
> 
> > I was pretty shocked when I heard that JDBC implements the low level protocol
> > itself. It seems like a dead-end strategy to me. Any new protocol innovations
> > need to be implemented in every driver. Every implementation gets the chance
> > to reimplement the same bugs and same inefficiencies over and over again.
> 
> There *are* benefits to implementing the protocol directly. First on my
> personal list is that our Java application would not be able to use postgresql
> at all if the driver required JNI/libpq.

Well benefits that boil down to "Java sucks" aren't very convincing. Perl
suffers from no such handicap. There are tons of Perl modules that have C
implementations. Sometimes simply for speed or convenience. Perl makes it
pretty convenient to write modules in C or interface with C libraries without
too much pain.

Hell, it's not the recommended way to make modules but for convenience you
can't really beat:

perl -e 'use Inline C=>q{void greet() {printf("Hello, world\n");}}; greet'

The only benefit for a pure-perl driver would be the ease of use for Windows
users. And that only really matters because Windows users tend to be more
averse to using a compiler and often don't even have one installed.

> There are also some things in the JDBC API that seem hard to map to the current
> libpq API, e.g. streaming parameter data from a Java stream without taking an
> intermediate copy.

Ah, this is another problem. It boils down to "libpq sucks" at least for the
needs of a driver writer. I'm becoming convinced that libpq's problem was that
it's trying to satisfy two users, C programmers using postgres directly and
driver authors who just want a low level interface to the protocol.

What I'm trying to decide is whether the best course of action is to write a
different implementation for a perl driver (either in Perl or in C) or to fix
libpq to be more useful for driver authors.

> The protocol implementation is not really all that complex. The implementation
> for both V2 and V3 weighs in at ~6k lines of Java out of ~38k total, and much
> of that is connection-state juggling that we'd have to do anyway if using libpq
> (working out when to send BEGIN, breaking up multiple-statement queries into
> individual statements and matching the results up, managing portal state, etc).

I'll have to look at these things more closely. I wonder whether it makes
sense for JDBC, ODBC, DBD::Pg to all have independent implementations of these
features. 

Incidentally, does the JDBC spec really allow for multiple-statement queries
at all?

-- 
greg


16 Sep 2004 00:20:36 -0400
Greg Stark <gsstark@mit.edu>
Re: libpq and prepared statements progress for 8.0
Greg Stark wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
> 
>>There *are* benefits to implementing the protocol directly. First on my
>>personal list is that our Java application would not be able to use postgresql
>>at all if the driver required JNI/libpq.
> 
> 
> Well benefits that boil down to "Java sucks" aren't very convincing. Perl
> suffers from no such handicap.

Arguing that Java-specific benefits are not convincing benefits for a 
JDBC driver because you don't get them in Perl seems a bit odd to me. 
You're not implementing the driver in Perl!

Anyway, it's not a language issue so much as a support issue. We're not 
in a position to build and support libpq and a JNI interface to it on a 
large range of hardware platforms, but we can get 3rd party support for 
JVMs on those platforms just fine.

> Incidentally, does the JDBC spec really allow for multiple-statement queries
> at all?

No, but it's a common extension, and earlier driver versions (talking 
only V2) supported it.

-O

Thu, 16 Sep 2004 16:45:55 +1200
Oliver Jowett <oliver@opencloud.com>
Re: libpq and prepared statements progress for 8.0

Oliver Jowett <oliver@opencloud.com> writes:

> > Well benefits that boil down to "Java sucks" aren't very convincing. Perl
> > suffers from no such handicap.
> 
> Arguing that Java-specific benefits are not convincing benefits for a JDBC
> driver because you don't get them in Perl seems a bit odd to me. You're not
> implementing the driver in Perl!

Er, we're kind of on two different wavelengths here. What I'm trying to
determine are what are the benefits of writing a pure-perl driver versus one
that implements the protocol in a C module, versus one that merely interfaces
with libpq.

The current Perl module interfaces with libpq. The closest analogue to use for
comparison is the JDBC driver which is a pure-Java implementation. So the
benefits and disadvantages the JDBC driver faces are useful data points.
However benefits that arise purely because of quirks of Java and don't relate
to Perl are less relevant than benefits and disadvantages that are more
general.

I wasn't trying to criticize the decisions behind the JDBC implementation. It
may well be that the choice that makes sense for Java isn't the same as the
choice that makes sense in other languages. Or it may be that there are
lessons that can be learned from Java that generalize to other languages and
a pure perl implementation may make sense.

-- 
greg


16 Sep 2004 01:11:38 -0400
Greg Stark <gsstark@mit.edu>
Re: libpq and prepared statements progress for 8.0
On Wed, 15 Sep 2004 13:50:43 -0400, tgl@sss.pgh.pa.us (Tom Lane)
wrote:

>
>Well, (a) I ran out of time, and (b) I wasn't sure what the most
>convenient API would be.  Should we create something that packages
>together a Parse and a Describe Statement, or expose those as
>separate things?
>
>There's surely no technical difficulty once you've got some consensus
>on what the API should look like.
>
>
>			regards, tom lane

Having a separate Parse and Describe would fit neatly with the ODBC
model. Hopefully the ODBC driver will get worked on in the medium term
to use libpq.

Regards,
Gary.


Thu, 16 Sep 2004 09:20:25 +0100
Gary Doades <gpd@cwcom.net>
Re: libpq and prepared statements progress for 8.0
The odbc driver must be doing the same thing, as well I suspect pgadmin
has a protocol stack built into it as well?

There is a jdbc driver for postgresql on sourceforge that does use
libpq. The fact that it is not widely used should be educational.

Dave
On Thu, 2004-09-16 at 01:11, Greg Stark wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
> 
> > > Well benefits that boil down to "Java sucks" aren't very convincing. Perl
> > > suffers from no such handicap.
> > 
> > Arguing that Java-specific benefits are not convincing benefits for a JDBC
> > driver because you don't get them in Perl seems a bit odd to me. You're not
> > implementing the driver in Perl!
> 
> Er, we're kind of on two different wavelengths here. What I'm trying to
> determine are what are the benefits of writing a pure-perl driver versus one
> that implements the protocol in a C module, versus one that merely interfaces
> with libpq.
> 
> The current Perl module interfaces with libpq. The closest analogue to use for
> comparison is the JDBC driver which is a pure-Java implementation. So the
> benefits and disadvantages the JDBC driver faces are useful data points.
> However benefits that arise purely because of quirks of Java and don't relate
> to Perl are less relevant than benefits and disadvantages that are more
> general.
> 
> I wasn't trying to criticize the decisions behind the JDBC implementation. It
> may well be that the choice that makes sense for Java isn't the same as the
> choice that makes sense in other languages. Or it may be that there are
> lessons that can be learned from Java that generalize to other languages and
> a pure perl implementation may make sense.
-- 
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com


Fri, 17 Sep 2004 09:32:03 -0400
Dave Cramer <pg@fastcrypt.com>
Re: libpq and prepared statements progress for 8.0