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.

Interesting,

I'd like to know more about the issues you are trying to solve? Seems
counter productive for all of us to attempt this independently

What about the .net driver ? Yet another protocol implementation ?

Dave
On Fri, 2004-09-17 at 09:36, Dave Page wrote:

-----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.

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

--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

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

Interesting,

I'd like to know more about the issues you are trying to
solve? Seems counter productive for all of us to attempt this
independently

Mainly lack of time :-)

SSL and v3+ protocol support are the primary issues. If we can use
libpq, then obviously we don't have to worry about them any more. Other
than that, there isn't then a great deal left to do with the driver
(that I can think of) as the vast majority of the API is there and
working, and we have unicode support as well now.

What about the .net driver ? Yet another protocol implementation ?

Yeah, native, managed C# code.

Regards, Dave

"Dave Page" <dpage@vale-housing.co.uk> writes:

From: Dave Cramer [mailto:pg@fastcrypt.com]
I'd like to know more about the issues you are trying to
solve? Seems counter productive for all of us to attempt this
independently

Mainly lack of time :-)

SSL and v3+ protocol support are the primary issues. If we can use
libpq, then obviously we don't have to worry about them any more.

Another point is that since the ODBC driver is C anyway, there are no
cross-language issues for it in relying on libpq. This is quite
different IMHO from the situation for JDBC, or DBD::Pg, where there are
advantages in having a single-language solution.

BTW, there is also a pure-Tcl client implementation out there, written
despite the pre-existence of a version sitting atop libpq. Same story:
needing some C code reduces portability, or at least ease of installation.

regards, tom lane

On Wed, 15 Sep 2004 13:50:43 -0400, Tom Lane wrote:

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 just finished reading through this thread in the archives, and just
wanted to jump in here for a moment, if I could, to ask a couple of
questions to get a few things straight, at least in my mind.

To get initial PREPARE support, I don't think that the other DBD::Pg
developers mind sending strings to the PostgreSQL server in a SQL
PREPARE statement. I certainly don't. It will be great when the binary
API is there in libpq to exploit, but until it is, strings are fine.
This is how DBD::Oracle works, for what it's worth (it sends VARCHAR
bound parameters to the server, and the server figures out the
appropriate data type).

The question, in my mind, is it currently possible to do this without
specifying the type of every placeholder? As Greg Stark points out, it
would be nice to have an API to get this information from the server,
but it's not really important in the short run if the server just takes
care of unknowns. I'm told that it does, but no one has been able to
help us with the syntax, AFAIK.

What we want, I think, is what Greg Stark asks about here:

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00399.php

And Greg Sabino Mullane was asking about the appropriate syntax here:

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00372.php

If we could just get this figured out and know that we can rely on the
server to determine the appropriate syntax for this, we'll have what we
need to get PREPAREd statements working in the next release of DBD::Pg.
Something like this (although this doesn't seem to work):

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

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.

I think that would be great, but I'm hoping it's not required to solve
our immediate problem.

Regards and thanks for the help,

David

David Wheeler <david@kineticode.com> writes:

To get initial PREPARE support, I don't think that the other DBD::Pg
developers mind sending strings to the PostgreSQL server in a SQL
PREPARE statement.

The question, in my mind, is it currently possible to do this without
specifying the type of every placeholder?

Well, that's the problem: you can't. The SQL PREPARE command doesn't
have any provision for dealing with unspecified parameters. I think
this is reasonable since if it could, it would still have no way to tell
you what it resolved the parameter types as. The shortcoming here is
really in libpq and not in the backend: the protocol-level Parse
operation *is* designed to handle this scenario, but libpq isn't letting
you get at it.

regards, tom lane

On Sep 17, 2004, at 1:32 PM, Tom Lane wrote:

Well, that's the problem: you can't. The SQL PREPARE command doesn't
have any provision for dealing with unspecified parameters. I think
this is reasonable since if it could, it would still have no way to
tell
you what it resolved the parameter types as. The shortcoming here is
really in libpq and not in the backend: the protocol-level Parse
operation *is* designed to handle this scenario, but libpq isn't
letting
you get at it.

Great, thank you, Tom, that's exactly the answer I was looking for. And
if I understand a previous post from you, this functionality should be
added to libpq, but you haven't had time and aren't sure what the API
should look like, anyway, is that correct?

Is this the task labeled "llow libpq to check parameterized data types"
here?:

http://candle.pha.pa.us/cgi-bin/pgopenitems

Regards,

David

David Wheeler <david@kineticode.com> writes:

if I understand a previous post from you, this functionality should be
added to libpq, but you haven't had time and aren't sure what the API
should look like, anyway, is that correct?

Right. Proposals welcome.

Is this the task labeled "llow libpq to check parameterized data types"
here?:
http://candle.pha.pa.us/cgi-bin/pgopenitems

I think that was something else, but memory is fuzzy.

regards, tom lane

On Sep 17, 2004, at 1:54 PM, Tom Lane wrote:

David Wheeler <david@kineticode.com> writes:

if I understand a previous post from you, this functionality should be
added to libpq, but you haven't had time and aren't sure what the API
should look like, anyway, is that correct?

Right. Proposals welcome.

Wish I spoke C! But I don't think it makes sense to support PREPARE in
DBD::Pg without this functionality. :-(

Is this the task labeled "allow libpq to check parameterized data
types"
here?:
http://candle.pha.pa.us/cgi-bin/pgopenitems

I think that was something else, but memory is fuzzy.

I'm less sure, since I was the one who asked Jan Wieck about this at
OSCON, for which I believe that this is the relevant discussion:

http://archives.postgresql.org/pgsql-hackers/2004-08/msg00136.php
http://archives.postgresql.org/pgsql-hackers/2004-08/msg00130.php

But maybe Jan and I misunderstood each other?

Regards,

David

David Wheeler <david@kineticode.com> writes:

Is this the task labeled "allow libpq to check parameterized data
types" here?:
http://candle.pha.pa.us/cgi-bin/pgopenitems

I think that was something else, but memory is fuzzy.

I'm less sure, since I was the one who asked Jan Wieck about this at
OSCON, for which I believe that this is the relevant discussion:

Okay, here we have Bruce saying he'll add it to open items:

http://archives.postgresql.org/pgsql-hackers/2004-08/msg00147.php

so I guess it is the same thing. Good, that will give us some political
cover for squeezing it into 8.0 ;-). Assuming that anyone steps up and
does the work, that is.

regards, tom lane

On Sep 17, 2004, at 2:21 PM, Tom Lane wrote:

Okay, here we have Bruce saying he'll add it to open items:

http://archives.postgresql.org/pgsql-hackers/2004-08/msg00147.php

so I guess it is the same thing. Good, that will give us some
political
cover for squeezing it into 8.0 ;-).

Ah, okay, so Jan and I weren't misunderstanding each other, then. :-)
Thanks Tom.

Assuming that anyone steps up and
does the work, that is.

So...any volunteers? I suspect that other driver projects would be
happy to have it, too.

Cheers,

David

Tom Lane wrote:

David Wheeler <david@kineticode.com> writes:

if I understand a previous post from you, this functionality should be
added to libpq, but you haven't had time and aren't sure what the API
should look like, anyway, is that correct?

Right. Proposals welcome.

Is this the task labeled "llow libpq to check parameterized data types"
here?:
http://candle.pha.pa.us/cgi-bin/pgopenitems

I think that was something else, but memory is fuzzy.

Yep, that is it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

David Wheeler <david@kineticode.com> writes:

To get initial PREPARE support, I don't think that the other DBD::Pg
developers mind sending strings to the PostgreSQL server in a SQL
PREPARE statement.

The question, in my mind, is it currently possible to do this without
specifying the type of every placeholder?

Well, that's the problem: you can't. The SQL PREPARE command doesn't
have any provision for dealing with unspecified parameters. I think
this is reasonable since if it could, it would still have no way to tell
you what it resolved the parameter types as.

But it sounds like the client doesn't care about the type info anyway;
it'd pass all the parameters as text and let the backend sort out the
types when doing implicit conversions in the EXECUTE parse step:

template1=> prepare s1(int) as select $1;
PREPARE
template1=> execute s1('12');
?column?
----------
12
(1 row)

(now replace "int" with "unknown"..)

-O

Oliver Jowett wrote:

template1=> prepare s1(int) as select $1;
PREPARE
template1=> execute s1('12');
?column?
----------
12
(1 row)

(now replace "int" with "unknown"..)

Ok, bad example since the backend probably can't infer a type for the
PREPARE in this case. A better example:

template1=> prepare s1(int) as select typname from pg_type where oid = $1;
PREPARE
template1=> execute s1('16');
typname
---------
bool
(1 row)

-O

On Sep 17, 2004, at 6:23 PM, Oliver Jowett wrote:

template1=> prepare s1(int) as select typname from pg_type where oid
= $1;
PREPARE
template1=> execute s1('16');
typname ---------
bool
(1 row)

You're still telling it the type via that int.

Regards,

David

David Wheeler wrote:

On Sep 17, 2004, at 6:23 PM, Oliver Jowett wrote:

template1=> prepare s1(int) as select typname from pg_type where oid
= $1;
PREPARE
template1=> execute s1('16');
typname ---------
bool
(1 row)

You're still telling it the type via that int.

Well, obviously. I haven't modified the backend code to accept 'unknown'
in PREPARE..

My point was the client does *not* need to know the type inferred by the
PREPARE in the 'unknown' case to make use of the resulting statement. It
can pass all parameters as text and use the type inference that happens
on EXECUTE -- as is happening in the EXECUTE quoted above.

-O

On Sep 18, 2004, at 1:09 PM, Oliver Jowett wrote:

Well, obviously. I haven't modified the backend code to accept
'unknown' in PREPARE..

Right, and that's what we're looking for.

My point was the client does *not* need to know the type inferred by
the PREPARE in the 'unknown' case to make use of the resulting
statement. It can pass all parameters as text and use the type
inference that happens on EXECUTE -- as is happening in the EXECUTE
quoted above.

Yes, that's fine, but it's PREPARE that's at issue here, not EXECUTE.

Regards,

David

David Wheeler wrote:

On Sep 18, 2004, at 1:09 PM, Oliver Jowett wrote:

Well, obviously. I haven't modified the backend code to accept
'unknown' in PREPARE..

Right, and that's what we're looking for.

My point was the client does *not* need to know the type inferred by
the PREPARE in the 'unknown' case to make use of the resulting
statement. It can pass all parameters as text and use the type
inference that happens on EXECUTE -- as is happening in the EXECUTE
quoted above.

Yes, that's fine, but it's PREPARE that's at issue here, not EXECUTE.

I think you misunderstand what I'm saying.

Tom reckons that PREPARE (at the SQL level) taking unknown types is not
useful as there is no feedback mechanism along the lines of the V3
protocol Describe messages to let the client find out what types were
inferred by the PREPARE.

I am saying this doesn't matter as the client can still use the
resulting statement just fine without knowing the types. So allowing
'unknown' in PREPARE *is* useful.

Not that I'm volunteering to implement it, though -- the JDBC driver
does not need this functionality and I'm way too short on time anyway :(

-O

Oliver Jowett <oliver@opencloud.com> writes:

Tom reckons that PREPARE (at the SQL level) taking unknown types is not
useful as there is no feedback mechanism along the lines of the V3
protocol Describe messages to let the client find out what types were
inferred by the PREPARE.

I am saying this doesn't matter as the client can still use the
resulting statement just fine without knowing the types. So allowing
'unknown' in PREPARE *is* useful.

Well, that was not quite my point, but I guess I wasn't clear. My
reasoning was more like this:
1. What we have now doesn't do what DBD::Pg needs.
2. We can fix it with some-small-amount-of-work in libpq (to add some API),
or with some-probably-also-small-amount-of-work in the backend (to
kluge up SQL PREPARE to allow "unknown").
3. The libpq-side solution is more generally useful, because it can support
feedback about the resolved datatypes.
4. Therefore, we should fix it in libpq.

Note that point 3 is not dependent on whether DBD::Pg in particular
needs this functionality --- somebody out there certainly will.

regards, tom lane

At 2004-09-17 14:28:36 -0700, david@kineticode.com wrote:

Assuming that anyone steps up and does the work, that is.

So...any volunteers?

OK, how about adding a PQprepare (PQcreatePrepared?) function like this?

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes)
{
...

PQprepare would construct a Parse message to create a prepared statement
named stmtName from the given query, with nParams types pre-declared. It
could be called by DBD::Pg with nParams == 0 to let the server infer all
of the parameter types.

I suppose an asynchronous equivalent would also be needed.
(Yes, I'm volunteering to write both functions.)

-- ams

On Sep 19, 2004, at 9:13 PM, Abhijit Menon-Sen wrote:

OK, how about adding a PQprepare (PQcreatePrepared?) function like
this?

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes)
{
...

PQprepare would construct a Parse message to create a prepared
statement
named stmtName from the given query, with nParams types pre-declared.
It
could be called by DBD::Pg with nParams == 0 to let the server infer
all
of the parameter types.

Sounds damn good to me, Abhihit, thanks!

I suppose an asynchronous equivalent would also be needed.
(Yes, I'm volunteering to write both functions.)

Woot! :-)

Regards,

David

Abhijit Menon-Sen <ams@oryx.com> writes:

OK, how about adding a PQprepare (PQcreatePrepared?) function like this?

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes)
{
...

PQprepare would construct a Parse message to create a prepared statement
named stmtName from the given query, with nParams types pre-declared. It
could be called by DBD::Pg with nParams == 0 to let the server infer all
of the parameter types.

You have to have a parameter for specifying the portalName. Since using the
"unnamed" portal is no longer really an option.

That means you also need to add a new Execute method that takes a portalName
instead of a command.

I've been fooling around with this. But don't let that stop you, I haven't
said anything precisely because I figure my odds on getting this finished to
be quite low. And in any case I don't know if it'll be up to snuff for
inclusion.

I have a couple questions:

1) I expect any driver to automatically gensym up all the statement names and
portal names. I wonder whether libpq shouldn't be doing the same thing.
This goes back to the same issue of libpq trying to satisfy two masters. A
C programmer would be happy with libpq gensymming the statement names and
portal names, but a driver author would be just as happy doing it himself.

2) How is it that the error handling in the existing PQsendQueryGuts works at
all?! It just aborts wherever it's at and jumps to a function that
consumes all input. It has no idea where to stop consuming since the SYNC
may or may not have been sent. If there's a network burp wouldn't it cause
messages to get left over to confuse matters for the next call?

I wonder whether it would make sense to have SYNC messages come with an
integer parameter attached which gets echoed back by the server. Then libpq
error handling can just issue its own SYNC and wait until it gets back a SYNC
response with the correct value, rather than have to engineer the sending code
to keep careful track of whether the SYNC has been sent or not.

--
greg

At 2004-09-20 01:25:56 -0400, gsstark@mit.edu wrote:

That means you also need to add a new Execute method that takes a
portalName instead of a command.

Yes, thanks. How about these functions, then?

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes);

PGresult *
PQbind(PGconn *conn,
const char *stmtName,
const char *portalName,
int nParams,
const char *const *paramValues,
const int *paramLengths,
int nFormats,
const int *paramFormats,
int nResults,
const int *resultFormats);

PGresult *
PQexecute(PGconn *conn,
const char *portalName,
int nRows);

-- ams

Abhijit Menon-Sen <ams@oryx.com> writes:

At 2004-09-20 01:25:56 -0400, gsstark@mit.edu wrote:

That means you also need to add a new Execute method that takes a
portalName instead of a command.

Oh, it occurs to me I missed a step in my earlier logic. I assumed we would
want a separate Bind and execute call. Do we? I think we do, but the only
advantages are pretty thin:

1) It could be convenient to have an application call bind but have another
layer call execute. I've never seen this happen.

2) If we ever want to implement a Describe call then it would have to happen
between Bind and execute. Personally I find it annoying that you can't call
describe on a statement, only a portal, but that's the way it is now.

Yes, thanks. How about these functions, then?

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes);

PGresult *
PQbind(PGconn *conn,
const char *stmtName,
const char *portalName,
int nParams,
const char *const *paramValues,
const int *paramLengths,
int nFormats,
const int *paramFormats,

int nResults,
const int *resultFormats);

resultFormat is just a single integer in the protocol. You don't get to
specify different formats for different columns.

PGresult *
PQexecute(PGconn *conn,
const char *portalName,
int nRows);

What's nRows? None of the existing PQexec* take an nRows parameter.

--
greg

At 2004-09-20 02:16:50 -0400, gsstark@mit.edu wrote:

Personally I find it annoying that you can't call describe on a
statement, only a portal, but that's the way it is now.

No, it isn't. Describe accepts both prepared statement and portal names.
In the former case, it returns ParameterDescription message as well as
the RowDescriptions it returns for the latter.

resultFormat is just a single integer in the protocol. You don't get
to specify different formats for different columns.

Yes, you do. Bind accepts 0 (use the default text format), 1 (use this
format for all results), or as many result format specifiers as there
are results.

What's nRows? None of the existing PQexec* take an nRows parameter.

Execute can be told to return no more than n rows of results. If there
are more rows available, the server returns PortalSuspended and awaits
another Execute message. The default of 0 imposes no limit.

Please read protocol-flow.html and protocol-message-formats.html.

-- ams

There was some previous discussion of whether DBD:pg should continue
using libpq or implement the wire protocol in Perl, and whether ODBC
should move to using libpq.

I think we should favor libpq usage wherever possible and only
re-implement it in the native language when required, like for jdbc/java.
I think having all interfaces take advantage of libpq improvements and
features is a major win. If we need to add things to libpq to make it
easier, fine, but that is minor work compared to maintaining separate
wire protocol for each interface language.

---------------------------------------------------------------------------

Tom Lane wrote:

Oliver Jowett <oliver@opencloud.com> writes:

Tom reckons that PREPARE (at the SQL level) taking unknown types is not
useful as there is no feedback mechanism along the lines of the V3
protocol Describe messages to let the client find out what types were
inferred by the PREPARE.

I am saying this doesn't matter as the client can still use the
resulting statement just fine without knowing the types. So allowing
'unknown' in PREPARE *is* useful.

Well, that was not quite my point, but I guess I wasn't clear. My
reasoning was more like this:
1. What we have now doesn't do what DBD::Pg needs.
2. We can fix it with some-small-amount-of-work in libpq (to add some API),
or with some-probably-also-small-amount-of-work in the backend (to
kluge up SQL PREPARE to allow "unknown").
3. The libpq-side solution is more generally useful, because it can support
feedback about the resolved datatypes.
4. Therefore, we should fix it in libpq.

Note that point 3 is not dependent on whether DBD::Pg in particular
needs this functionality --- somebody out there certainly will.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

(I apologise in advance if anyone receives multiple copies of this post.
I posted from the wrong address earlier.)

At 2004-09-20 02:16:50 -0400, gsstark@mit.edu wrote:

I assumed we would want a separate Bind and execute call. Do we?

Yes, we do. (See below.)

Personally I find it annoying that you can't call describe on a
statement, only a portal, but that's the way it is now.

No, it isn't. Describe accepts both prepared statement and portal names.
In the former case, it returns ParameterDescription message as well as
the RowDescriptions it returns for the latter.

resultFormat is just a single integer in the protocol. You don't get
to specify different formats for different columns.

Yes, you do. Bind accepts 0 (use the default text format), 1 (use this
format for all results), or as many result format specifiers as there
are results.

What's nRows? None of the existing PQexec* take an nRows parameter.

Execute can be told to return no more than n rows of results. If there
are more rows available, the server returns PortalSuspended and awaits
another Execute message. The default of 0 imposes no limit.

Because it's sometimes required to call Execute without Binding values
again, libpq needs separate bind/execute functions to support this.

Please read protocol-flow.html and protocol-message-formats.html.

-- ams

Abhijit Menon-Sen <ams@oryx.com> writes:

(I apologise in advance if anyone receives multiple copies of this post.
I posted from the wrong address earlier.)

At 2004-09-20 02:16:50 -0400, gsstark@mit.edu wrote:

I assumed we would want a separate Bind and execute call. Do we?

I forgot the main reason I assumed this. Namely that drivers have to provide
this interface. If we don't provide it in libpq then they have to emulate it
by storing the parameters until execute time.

No, it isn't. Describe accepts both prepared statement and portal names.
In the former case, it returns ParameterDescription message as well as
the RowDescriptions it returns for the latter.

I see this now. I didn't realize you got back both sets of information when
describing a statement. Does Binding get any useful feedback? Does it tell you
at that time if the text input can't be parsed as the appropriate types for
example?

Yes, you do. Bind accepts 0 (use the default text format), 1 (use this
format for all results), or as many result format specifiers as there
are results.

Ooh, I was just looking at the existing code. This is indeed in the protocol
specs.

What's nRows? None of the existing PQexec* take an nRows parameter.

Execute can be told to return no more than n rows of results. If there
are more rows available, the server returns PortalSuspended and awaits
another Execute message. The default of 0 imposes no limit.

So this is the strongest argument for separating bind and execute. If you
didn't you would still need a second call executeContinue or something.

--
greg

Abhijit Menon-Sen <ams@oryx.com> writes:

Execute can be told to return no more than n rows of results. If there
are more rows available, the server returns PortalSuspended and awaits
another Execute message. The default of 0 imposes no limit.

Because it's sometimes required to call Execute without Binding values
again, libpq needs separate bind/execute functions to support this.

I don't really think so. Allowing access to the limited-row-count
version of Execute would fundamentally break the PGresult abstraction,
which thinks of a query result as a monolithic entity.

There has been talk from time to time of developing a new API (possibly
a whole new library?) that would allow streaming access, but I would
strongly urge you not to try to solve that problem at the same time;
if only because there is zero chance of such a patch being accepted
within the 8.0 cycle.

In my mind the existing PQexecPrepared operation is all you need to
support binding and execution of prepared statements. What you should
be concerned with right now is providing an API for Parse + Describe
Statement, to substitute for the existing approach of setting up
statement objects via PQexec("PREPARE foo ...").

regards, tom lane

At 2004-09-20 10:20:03 -0400, tgl@sss.pgh.pa.us wrote:

What you should be concerned with right now is providing an API for
Parse + Describe Statement, to substitute for the existing approach
of setting up statement objects via PQexec("PREPARE foo ...").

Fair enough. That's why my original proposal was to add only a PQprepare
function, which should be a patch small enough to write, test, and maybe
apply before 8.0:

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes);

Should I go ahead and do that?

-- ams

Abhijit Menon-Sen <ams@oryx.com> writes:

PGresult *
PQprepare(PGconn *conn,
const char *stmtName,
const char *query,
int nParams,
const Oid *paramTypes);

Should I go ahead and do that?

(1) What about preparing an unnamed statement ... will you allow
stmtName = NULL to mean that? (Actually it might be that stmtName = ""
will cover this, I'm too lazy to check the backend code right now.)
You do need to allow access to the unnamed statement because of
Oliver Jowett's recent efficiency hacks.

(2) What about discovering the actually resolved parameter types?

regards, tom lane

At 2004-09-20 11:02:50 -0400, tgl@sss.pgh.pa.us wrote:

(1) What about preparing an unnamed statement ...

stmtName == "" will work.

(2) What about discovering the actually resolved parameter types?

I'm not sure what to do about that.

I could extend PGresult to hold ParameterDescription information, then
provide accessor functions à la PQnfields/PQfformat. But that would be
a fairly intrusive change. And I shudder to even think about trying to
reuse the existing PGresult fields/accessors to do the job.

Do you have any suggestions?

-- ams

On Sep 20, 2004, at 12:34 AM, Bruce Momjian wrote:

I think we should favor libpq usage wherever possible and only
re-implement it in the native language when required, like for
jdbc/java.
I think having all interfaces take advantage of libpq improvements and
features is a major win. If we need to add things to libpq to make it
easier, fine, but that is minor work compared to maintaining separate
wire protocol for each interface language.

I don't normally post "me too" posts, but I think that what Bruce says
here is extremely important. The more drivers can rely on a single,
well-developed, and stable API to create a variety of drivers, the less
work *everyone* has to do. I think that this kind of pragmatic
componentization (to coin a phrase) can only be to the benefit of
PostgreSQL.

Regards,

David

PS: And for those who really want a Pure Perl implementation of a
PostgreSQL driver in Perl, I suggest you take a look at helping out
with DBD::PgPP:

http://search.cpan.org/dist/DBD-PgPP/

David Wheeler <david@kineticode.com> writes:

On Sep 20, 2004, at 12:34 AM, Bruce Momjian wrote:

I think we should favor libpq usage wherever possible and only
re-implement it in the native language when required, like for
jdbc/java.

I don't normally post "me too" posts, but I think that what Bruce says
here is extremely important.

Allow me to state a contrary position ;-)

The first problem with this approach is that it requires libpq to be all
things to all people. We've already had some discussion in this thread
about the tension between supporting application programs written in C,
which want one set of features, and drivers, which need some other ones.
After awhile you end up with a bloated, probably buggy library. We're
already some way down that path, and I don't care to go much further.

The second problem is the one someone already pointed out, that you
*need* multiple implementations in order to keep the protocol definition
honest.

I don't necessarily disagree about the immediate issues. I think it
would be a win to reimplement the ODBC driver atop libpq (if it's a
comfortable fit --- but not if we have to add warts to libpq to make
it work). And I don't feel any strong need to redo DBD::Pg as a
native-Perl driver. But I disagree that either of those decisions
should be taken on the basis of an "everyone should use libpq"
philosophy. Rather they should be taken on the basis of what makes
sense for each of those projects individually.

regards, tom lane

Abhijit Menon-Sen <ams@oryx.com> writes:

At 2004-09-20 11:02:50 -0400, tgl@sss.pgh.pa.us wrote:

(2) What about discovering the actually resolved parameter types?

Do you have any suggestions?

It depends on whether you think that PQprepare should bundle the
Describe Statement operation or not. You can make a good argument
either way: (a) for apps or drivers that don't need to find out the
actual arg types, bundling in the Describe would be a waste of cycles;
but (b) if you need the Describe then it's a loss to have to spend an
extra network round trip to get the results. Neither of these overheads
is compellingly large, though.

If you want it unbundled then a separate function is easy enough,
perhaps
PGresult * PQdescribeStatement(PGconn *conn,
const char *stmtName,
int *numParams,
Oid **paramTypes);
where *paramTypes receives a pointer to a malloc'd array (caller
to free after use); or null on failure. The PGresult would just
be used to convey success/failure.

If you want it bundled, perhaps add output parameters defined
similarly to the above to PQprepare.

It'd be possible to handle both cases in PQprepare: add the out
parameters, but say that passing NULL for them indicates the Describe
step is not wanted. I dunno if that's too complicated.

You had mentioned wanting to support async operation. We couldn't very
reasonably support async operation with separate output parameters ---
it would be a lot cleaner if the param type list were embedded in the
PGresult instead, so that PQgetResult would be sufficient. I understand
your distaste for multiplexing the use of the PGresult fields, but still
I think it would be most sensible to define PQnfields() and PQftype()
as the way to extract the information from the PGresult if we go that
way. We could invent a new PQresultStatus, say PGRES_PREPARE_OK,
as a way to distinguish a PGresult of this kind from the normal
query-result object.

If you prefer this last case then I think the cleanest approach is just
to automatically bundle the Describe operation into PQprepare. You
could imagine adding a boolean to PQprepare's param list to specify
whether you care about getting valid parameter type info back or not,
but I think that looks way too much like a wart.

If you need more options, I can probably think of some ;-)

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another point is that since the ODBC driver is C anyway, there are no
cross-language issues for it in relying on libpq. This is quite
different IMHO from the situation for JDBC, or DBD::Pg, where there are
advantages in having a single-language solution.

This is not so much of an issue with DBD::Pg - it already uses quite a
bit of C code[1]Well, C and pseudo-C XS code anyway.. not only from libpq, but because DBI is written in C, and
because it is handy sometimes to use non-libpq pgsql code, such as some
of the quoting stuff. Having libpq is definitely a bonus for us (DBD::Pg),
and we've never really had an issue with libpq until now, and it's getting
fixed, so no worries.

[1]: Well, C and pseudo-C XS code anyway.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409202318
-----BEGIN PGP SIGNATURE-----

iD8DBQFBT52RvJuQZxSWSsgRAvWXAJ9FTLnVzFULYKEF1Z16EnNJw6BxewCdFHW5
13hbl5M6pT9+oezDpAVetIM=
=Dpjb
-----END PGP SIGNATURE-----

In article <12594.1095699940@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

David Wheeler <david@kineticode.com> writes:

On Sep 20, 2004, at 12:34 AM, Bruce Momjian wrote:

I think we should favor libpq usage wherever possible and only
re-implement it in the native language when required, like for
jdbc/java.

I don't normally post "me too" posts, but I think that what Bruce says
here is extremely important.

Allow me to state a contrary position ;-)

The first problem with this approach is that it requires libpq to be all
things to all people. We've already had some discussion in this thread
about the tension between supporting application programs written in C,
which want one set of features, and drivers, which need some other ones.
After awhile you end up with a bloated, probably buggy library. We're
already some way down that path, and I don't care to go much further.

I don't think that's what David meant, although he said so :-)

What we should have is a C API especially for use by driver authors;
probably this API is so far away from the rest of libpq that it should
not be part of it.

This API could make life easier for driver authours, resulting in more
and better drivers for more languages.

The second problem is the one someone already pointed out, that you
*need* multiple implementations in order to keep the protocol definition
honest.

Nobody forces a driver author to use that API, and there are driver
authors who *cannot* use it, e.g. Java. This means there will be more
than one implementation anyways.

Harald Fuchs wrote:

The first problem with this approach is that it requires libpq to be all
things to all people. We've already had some discussion in this thread
about the tension between supporting application programs written in C,
which want one set of features, and drivers, which need some other ones.
After awhile you end up with a bloated, probably buggy library. We're
already some way down that path, and I don't care to go much further.

I don't think that's what David meant, although he said so :-)

What we should have is a C API especially for use by driver authors;
probably this API is so far away from the rest of libpq that it should
not be part of it.

OLE DB is based on libpq. While the proposed function would be very nice
to have (and, in fact, needed for some obscure semantics of the OLE DB
protocol that no one really uses), at the moment there are NO major
features missing from OLE DB that cannot be provided using the existing
code. This may be a result of libpq going some way down bloat av., as
Tom said, but personally I don't see the need for a separate API.

I have not delved too deeply into the ODBC sources, so I can't attest to
the feasibility of using libpq there.

This API could make life easier for driver authours, resulting in more
and better drivers for more languages.

I'm really interested in what this would provide. It could be that I'm
missing something painfully obvious here, but why are driver developers
in such a different situation than end users?

Don't get me wrong. Having an API to fill data from the server directly
into user's buffers would be nice. However, as OLE DB transfers data in
binary, as most data types require conversion, and as some of the OLD DB
"accessors" are really weird, I doubt a sane API can be written that I'd
use anyways.

Likewise, having an API that does gradual delivery of data would be
nice. However, things really can be achieved using the asynchronous
libpq mechanism, and proper cursors can achieve most of the rest.

In short, I may be missing something painfully simple here, but I don't
see the real need for a driver oriented backend communication library.

Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/

At 2004-09-20 13:24:47 -0400, tgl@sss.pgh.pa.us wrote:

It depends on whether you think that PQprepare should bundle the
Describe Statement operation or not.

I decided against bundling the two operations together. Here's a patch
to add PQprepare() and PQsendPrepare() in a fairly self-contained way.
Also attached is a test program à la testlibpq3.c that I used to test
the change. This should be all that's needed for DBD::Pg to prepare a
statement without pre-specifying types.

(I'll post a separate patch for PQdescribe() later. It's a little more
involved, and I need to fix a couple of bugs I found during testing.)

Any thoughts? Does this look good enough for 8.0?

-- ams

On Oct 5, 2004, at 9:59 AM, Abhijit Menon-Sen wrote:

I decided against bundling the two operations together. Here's a patch
to add PQprepare() and PQsendPrepare() in a fairly self-contained way.
Also attached is a test program à la testlibpq3.c that I used to test
the change. This should be all that's needed for DBD::Pg to prepare a
statement without pre-specifying types.

Ah, fantastic news, Abhijit! This is very exciting for DBD::Pg
development, as well as other dynamic language libraries that plan to
use prepare(), I expect. I very much look forward to Beta 4 hoping that
this patch makes it in.

Thanks for doing this!

Regards,

David

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of David Wheeler
Sent: Tuesday, October 05, 2004 10:32 AM
To: Abhijit Menon-Sen
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0

On Oct 5, 2004, at 9:59 AM, Abhijit Menon-Sen wrote:

I decided against bundling the two operations together.

Here's a patch

to add PQprepare() and PQsendPrepare() in a fairly

self-contained way.

Also attached is a test program à la testlibpq3.c that I

used to test

the change. This should be all that's needed for DBD::Pg to

prepare a

statement without pre-specifying types.

Ah, fantastic news, Abhijit! This is very exciting for DBD::Pg
development, as well as other dynamic language libraries that plan to
use prepare(), I expect. I very much look forward to Beta 4
hoping that
this patch makes it in.

It offers to all programmers who take advantage of it a valuable performance increase.
Especially in the case of programs that will reuse a statement many times with different parameter markers, it should be very valuable.
Most data entry jobs are like that.

You can make reuse of statements fairly transparent in some special cases.

Create a 64 bit hash (e.g. UMAC) of the prepared statement (removing hardwired parameters as needed so that "SELECT Col1, col2 FROM Some_Table where FOO = 'BAR'" becomes "SELECT COL1, COL2 FROM SOME_TABLE WHERE FOO = ?", form consistent capitalization of the statement by capitalizing all keywords and non-quoted column names and then form a hash. Create a hash table of skiplists that contain the prepared statement and the prepared statement handle (the hash modulo or bitmasked with some number is the index to which skiplist to store the data in). Then, when you get a query, if it is not already prepared, prepare it and store it in the list. If you find it in the list just reuse it. Of course, it only works with sticky cursors.

For something like TPC benchmarks, it can mean very large savings in time.

Any time you have a storm of small, similar queries, think 'prepared statement'

IMO-YMMV

On Oct 5, 2004, at 10:47 AM, Dann Corbit wrote:

Create a 64 bit hash (e.g. UMAC) of the prepared statement (removing
hardwired parameters as needed so that "SELECT Col1, col2 FROM
Some_Table where FOO = 'BAR'" becomes "SELECT COL1, COL2 FROM
SOME_TABLE WHERE FOO = ?", form consistent capitalization of the
statement by capitalizing all keywords and non-quoted column names and
then form a hash. Create a hash table of skiplists that contain the
prepared statement and the prepared statement handle (the hash modulo
or bitmasked with some number is the index to which skiplist to store
the data in). Then, when you get a query, if it is not already
prepared, prepare it and store it in the list. If you find it in the
list just reuse it. Of course, it only works with sticky cursors.

For something like TPC benchmarks, it can mean very large savings in
time.

Any time you have a storm of small, similar queries, think 'prepared
statement'

Yes, this is how the Perl DBI works. And with Abhijit's patch, DBD::Pg
(the DBI driver for PostgreSQL) will finally be able to take advantage
of it.

Regards,

David

Abhijit Menon-Sen <ams@oryx.com> writes:

I decided against bundling the two operations together. Here's a patch
to add PQprepare() and PQsendPrepare() in a fairly self-contained way.
Any thoughts? Does this look good enough for 8.0?

Seems OK as far as it goes, but a complete patch would require
documentation additions. Also you missed adding entry points in the
.dll files, and possibly other minor things. Searching for all
references to one of the existing entry points such as PQexecPrepared
will probably help you identify what you need to do.

regards, tom lane

At 2004-10-05 17:48:27 -0400, tgl@sss.pgh.pa.us wrote:

Searching for all references to one of the existing entry points such
as PQexecPrepared will probably help you identify what you need to do.

OK. I've attached two additional patches below.

I don't really understand how the *.def files work, so I'm just guessing
about what needs to be changed. Thanks to Josh Berkus and Kris Jurka for
looking over the documentation patch, which just adds descriptions of
the two new functions.

I apologise in advance if I've missed anything.

-- ams

Abhijit Menon-Sen <ams@oryx.com> writes:

--- fe-protocol3.c.1~	2004-10-05 18:59:55.293092244 +0530
+++ fe-protocol3.c	2004-10-05 19:17:48.154807848 +0530
@@ -220,6 +220,11 @@ pqParseInput3(PGconn *conn)
conn->asyncStatus = PGASYNC_READY;
break;
case '1':		/* Parse Complete */
+					if (conn->result == NULL)
+						conn->result = PQmakeEmptyPGresult(conn,
+														   PGRES_COMMAND_OK);
+					conn->asyncStatus = PGASYNC_READY;
+					break;
case '2':		/* Bind Complete */
case '3':		/* Close Complete */
/* Nothing to do for these message types */

So why is this part of the patch ok? Isn't it going to make libpq get confused
every time a PQExecPrepared sends a v3.0 prepare message? It will mark the
connection as PGASYNC_READY as soon as the prepare response is parsed instead
of waiting for the responses from the bind and execute messages that have
already been sent.

This is more or less where I got stuck on my attempt at the same thing. It
seems like to handle "bundled" calls like PQExecPrepared libpq would have to
keep track of from what call various messages arose. That seems like it would
complicate things quite a bit.

--
greg

(I apologise for the delayed response.)

At 2004-10-07 01:23:56 -0400, gsstark@mit.edu wrote:

So why is this part of the patch ok? Isn't it going to make libpq get
confused every time a PQExecPrepared sends a v3.0 prepare message?

I thought about that for a while, but I couldn't find anything that is
actually broken or confused by the patch. I could be missing something
obvious, though, so I'd appreciate another set of eyes looking at it.

Does anyone have any ideas?

-- ams

Dann Corbit wrote:

Create a 64 bit hash (e.g. UMAC) of the prepared statement (removing
hardwired parameters as needed so that "SELECT Col1, col2 FROM

Some_Table

where FOO = 'BAR'" becomes "SELECT COL1, COL2 FROM SOME_TABLE WHERE

FOO =

?", form consistent capitalization of the statement by capitalizing

all

keywords and non-quoted column names and then form a hash. Create a

hash

table of skiplists that contain the prepared statement and the

prepared

statement handle (the hash modulo or bitmasked with some number is the
index to which skiplist to store the data in). Then, when you get a
query, if it is not already prepared, prepare it and store it in the

list.

If you find it in the list just reuse it. Of course, it only works

with

sticky cursors.

For something like TPC benchmarks, it can mean very large savings in

time.

Any time you have a storm of small, similar queries, think 'prepared
statement'

IMO-YMMV

I do exactly this. The performance gain on such queries can be
enormous. For fast, simple queries (select a,b from t where k), the
turnaround time is about half when using prepared statements. The
overhead of caching them on the client is a small price to pay. This
performance gain is on top of a roughly 10-15% gain by utilizing the
parameterized interfaces (ExecParams and ExecPrepared).

Are these enhancements to the libpq interface going to allow a faster
way to fire prepared statements? In other words, is the proposal a
faster method than ExecPrepared?

Merlin

On Oct 14, 2004, at 6:50 PM, Abhijit Menon-Sen wrote:

I thought about that for a while, but I couldn't find anything that is
actually broken or confused by the patch. I could be missing something
obvious, though, so I'd appreciate another set of eyes looking at it.

Does anyone have any ideas?

Not I, but I still have my fingers crossed that this will go in in time
for 8.0. I think that Tom said it looked good when you first posted the
patch.

So, core hackers, is it going in or not? The dynamic language driver
developers will thank you for it!

Many thanks,

David

It was just added to CVS!

---------------------------------------------------------------------------

David Wheeler wrote:

On Oct 14, 2004, at 6:50 PM, Abhijit Menon-Sen wrote:

I thought about that for a while, but I couldn't find anything that is
actually broken or confused by the patch. I could be missing something
obvious, though, so I'd appreciate another set of eyes looking at it.

Does anyone have any ideas?

Not I, but I still have my fingers crossed that this will go in in time
for 8.0. I think that Tom said it looked good when you first posted the
patch.

So, core hackers, is it going in or not? The dynamic language driver
developers will thank you for it!

Many thanks,

David

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Oct 18, 2004, at 3:12 PM, Bruce Momjian wrote:

It was just added to CVS!

Awesome!

Abhijit++
Bruce++
Tom++

Regards,

David

On Mon, Oct 18, 2004 at 06:12:29PM -0400, Bruce Momjian wrote:

It was just added to CVS!

Woohooo!!!!!

Big, BIG kudos to all involved :) :)

Cheers,
D(BD::Pg)
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!