Thread: libpq and prepared statements progress for 8.0
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 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); (Yes, I know st(int) works, but DBD::Pg is not going to become a SQL parser, that's the backend's job, so we need a way to force unknown, or a way to return the data types back to us when we prepare a statement. The latter is on the 8.0 todo list, but have not seen any progress onit) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200409132114 -----BEGIN PGP SIGNATURE----- iD8DBQFBRkYqvJuQZxSWSsgRAnYoAKDicZ62t5N2kLNx7zDJRd3t9J7YTgCg7uVE xugi93g3Av2Smm8TkMz8MCk= =IwEf -----END PGP SIGNATURE-----
> 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
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, Pennsylvania19073
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 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.
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-workin 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,
intnParams, 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'tbe doing the same thing. This goes back to the same issue of libpq trying to satisfy two masters. A C
programmerwould be happy with libpq gensymming the statement names and portal names, but a driver author would be
justas 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
andjumps to a function that consumes all input. It has no idea where to stop consuming since the SYNC may or may
nothave been sent. If there's a network burp wouldn't it cause messages to get left over to confuse matters for the
nextcall?
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, intnParams, const Oid *paramTypes); PGresult * PQbind(PGconn *conn, const char *stmtName, const char *portalName, int nParams, const char *const *paramValues, const int *paramLengths, int nFormats, constint *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 findit 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
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, Pennsylvania19073
(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,
perhapsPGresult * 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]. 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 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
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
Attachment
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 bevery 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 capitalizationof the statement by capitalizing all keywords and non-quoted column names and then form a hash. Create a hashtable of skiplists that contain the prepared statement and the prepared statement handle (the hash modulo or bitmaskedwith some number is the index to which skiplist to store the data in). Then, when you get a query, if it is notalready prepared, prepare it and store it in the list. If you find it in the list just reuse it. Of course, it onlyworks 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
Attachment
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, Pennsylvania19073
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!