libpq and prepared statements progress for 8.0

Anyone working on the libpq interface to prepared statements?
We could really use that for DBD::Pg. Alternatively, if someone
knows a trick to prepare an INSERT statement without knowing
the data types, that would be neat too. For example:

CREATE TABLE foobar(a INTEGER);

These don't work:

PREPARE st(text) AS INSERT INTO foobar(a) VALUES ($1);

PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1);

PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1::unknown);

Using PQExecParams is completely out of the question? How are you
executing your statements...PQExec?

Merlin

PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1);

Using PQExecParams is completely out of the question? How are you
executing your statements...PQExec?

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.

Ideally the database should treat the placeholder exactly as it would a
single-quoted constant. Ie, it should treat it as "unknown" and use the
context around it to determine what type reader function to use to read it.

That would mean the semantics would be exactly equivalent to:

SELECT col_a FROM tab WHERE col_b = '1';

Without this capability using prepared statements is simply too cumbersome for
application programmers. And in any case DBD::Pg wants to maintain backwards
compatibility with the existing drivers which don't require binding parameter
types because they simply interpolate the parameters into the query string.

--
greg

Greg Stark <gsstark@mit.edu> writes:

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.

Really?

It would be cool if they'd stand up and identify themselves and mention
their concerns to the people who are doing the protocol and libpq work.
I sure haven't ever heard word one from anyone working on DBD::Pg.

I'll restrain myself from any stronger comments...

regards, tom lane

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

"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

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

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

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Wednesday, September 15, 2004 10:51 AM
To: Greg Stark
Cc: Merlin Moncure; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] 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.

What about using ECPG as an interface for drivers?

I wrote an ODBC driver for Rdb on OpenVMS using SQLMOD (which is
[essentially] ECPG for Rdb) so that is a technique that should be able
to provide the needed functionality.

According to my understanding ECPG is now reentrant. Is that correct?

"Dann Corbit" <DCorbit@connx.com> writes:

What about using ECPG as an interface for drivers?

What for? It's not a substitute for libpq --- it sits on top of libpq,
or did last I checked anyway. And it's designed around a preprocessor
that seems fairly useless for a driver.

regards, tom lane

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

"Dann Corbit" <DCorbit@connx.com> writes:

What about using ECPG as an interface for drivers?

What for? It's not a substitute for libpq --- it sits on top of libpq,
or did last I checked anyway. And it's designed around a preprocessor
that seems fairly useless for a driver.

As it happens DBD::Oracle does use Oracle's precompiler. But it's more of a
hindrance than a help. It basically has to define and implement its own API
which is compiled with Pro*C. Then the rest of the codebase can ignore the
precompiler and use that interface.

Precompilers are really old school. There's not much point to using them
except in legacy applications that need them. They offer no advantage over a
programmatic API, and lots of disadvantages. You have to learn a new language,
you're one step further removed from the resulting code, and heaven help you
if your compiler version doesn't match what the precompiler was tested with.
Nevermind actually trying to use it from another language entirely.

--
greg

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

-----Original Message-----
From: gsstark@mit.edu [mailto:gsstark@mit.edu]
Sent: Wednesday, September 15, 2004 11:34 AM
To: Tom Lane
Cc: Dann Corbit; Greg Stark; Merlin Moncure;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0

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

"Dann Corbit" <DCorbit@connx.com> writes:

What about using ECPG as an interface for drivers?

What for? It's not a substitute for libpq --- it sits on top of
libpq, or did last I checked anyway. And it's designed around a
preprocessor that seems fairly useless for a driver.

As it happens DBD::Oracle does use Oracle's precompiler. But
it's more of a hindrance than a help. It basically has to
define and implement its own API which is compiled with
Pro*C. Then the rest of the codebase can ignore the
precompiler and use that interface.

Precompilers are really old school. There's not much point to
using them except in legacy applications that need them. They
offer no advantage over a programmatic API, and lots of
disadvantages. You have to learn a new language, you're one
step further removed from the resulting code, and heaven help
you if your compiler version doesn't match what the
precompiler was tested with. Nevermind actually trying to use
it from another language entirely.

The nice thing about using the SQL precompiler was that it was very
simple.

A total of 15 interfaces needed to be defined:
PROCEDURE PREPARE_STMT
PROCEDURE DESCRIBE_SELECT
PROCEDURE DESCRIBE_PARM
PROCEDURE EXECUTE_STMT
PROCEDURE EXECUTE_IMMEDIATE
PROCEDURE DECLARE_CURSOR
PROCEDURE DECLARE_LIST_CURSOR
PROCEDURE DECLARE_CURSOR_READ_ONLY
PROCEDURE DECLARE_LIST_CURSOR_READ_ONLY
PROCEDURE DECLARE_CURSOR_INSERT_ONLY
PROCEDURE DECLARE_LIST_CURSOR_INSERT_ONLY
PROCEDURE OPEN_CURSOR
PROCEDURE FETCH_ROW
PROCEDURE CLOSE_CURSOR
PROCEDURE RELEASE_STMT

And each one was only a few lines (the entire SQLMOD file, including
comments is 5695 bytes long).
A C++ wrapper was written around these statements, and the C++ wrapper
is what is used from the applications that exercise the underlying
SQLMOD interface.

It was also considerably FASTER than using the C API directly. This is
probably due to Rdb being ANCIENT technology, and quite possibly the C
API is written over top of SQLMOD rather than the other way around.

At any rate, it is very simple to write a generic interface using a
precompiler.

However, if the PostgreSQL precompiler is not reentrant, it is a waste
of time. My thought was that ECPG might deliver the missing
functionality needed by the Perl interface. Since ECPG sits on top of
libpq, and libpq seems to lack some needed feature, it seems highly
unlikely that it can provide what is missing.

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

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

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

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

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.

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

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Dave Cramer
Sent: 17 September 2004 14:32
To: Greg Stark
Cc: Oliver Jowett; Tom Lane; Merlin Moncure;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0

The odbc driver must be doing the same thing,

It does, however we are considering a move to libpq if feasible to solve
some other issues.

as well I
suspect pgadmin has a protocol stack built into it as well?

<change hat>

We just use libpq with some simple wrapper classes for connection and
recordset handling.

Regards, Dave.