Dear Fellow DBI and PostgreSQL Hackers,

Apologies for cross-posting, but I figure that some of my questions can 
be better answered by DBI folks, while others can be better answered by 
PostgreSQL interface folks.

Since Tim pointed out that DBD::Pg hasn't been updated to use DBI's 
Driver.xst, I've taken it upon myself to try to update it to do so. 
However, since a) I've never programmed XS before; and b) I've never 
programmed C before; and c) I didn't want to just totally hork the 
DBD::Pg sources, I took it upon myself to try creating a new PostgreSQL 
driver from scratch.

The good news is that I think I'm making pretty decent progress, and I 
may well be able to get something workable in a few weeks. It's turning 
out that C isn't quite as tough to work with as my years-long mental 
block has led me to believe. Of course, it's made easier by the nicely 
done DBI::DBD document, as well as the great existing implementations 
for MySQL, ODBC, and Oracle. So I've been cutting and pasting with glee 
from the DBD::mysql and DBD::Pg sources, and I think it could add up to 
something pretty good before long.

All that is a long-winded way of leading up to some questions I've been 
having as I've worked through the sources. The questions:

* In DBD::Pg's dbdimp.c, the dbd_db_commit() function attempts a 
commit, and if it's successful, it then starts another transaction. Is 
this the proper behavior? The other DBDs I looked at don't appear to 
BEGIN a new transaction in the dbd_db_commit() function.

* A similar question applies to dbd_db_rollback(). It does a rollback, 
and then BEGINs a new transaction. Should it be starting another 
transaction there?

* How is DBI's begin_work() method intended to influence commits and 
rollbacks?

* Also in dbd_db_commit() and dbd_db_rollback(), I notice that the last 
return statement returns 0. Shouldn't these be returning true?

* In DBD::Pg's dbdimp.c, the dbd_db_disconnect() function automatically 
does a rollback if AutoCommit is off. Should there not be some way to 
tell that, in addition to AutoCommit being off, a transaction is 
actually in progress? That is to say, since the last call to 
dbd_db_commit() that some statements have actually been executed? Or 
does this matter?

* In dbd_db_destroy(), if I'm using Driver.xst, I don't actually need 
to execute this code, correct?

     if (DBIc_ACTIVE(imp_dbh)) {
         dbd_db_disconnect(dbh, imp_dbh);
     }

* In dbd_db_STORE_attrib(), DBD::Pg is doing the necessary stuff when 
AutoCommit is set to COMMIT and BEGIN transactions. If the answers to 
the above questions about dbd_db_commit() and dbd_db_rollback() 
indicate that they can stop BEGINing transactions, couldn't those 
functions be called inside dbd_db_STORE_attrib() instead of 
dbd_db_STORE_attrib() duplicating much of the same code?

* Also in dbd_db_STORE_attrib(), I note that DBD::Pg's 
imp_dbh->init_commit attribute is checked and set. Isn't this 
redundant, since we already have AutoCommit? Or could this attribute 
actually be used to tell something about the *status* of a transaction? 
(AFAICT, it currently isn't used that way, and is simply redundant).

* And finally, is dbd_preparse() totally necessary? I mean, doesn't 
PostgreSQL's PQexec() function do the necessary parsing? Jeffrey Baker 
mentioned to me that he was working on a new parser, and perhaps I'm 
missing something (because of parameters?), but I'm just trying to 
figure out why this is even necessary.

* One more thing: I was looking at the PostgreSQL documents for the new 
support for prepared statements in version 7.3. They look like this:

PREPARE q3(text, int, float, boolean, oid, smallint) AS
	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);

(BTW, I can see why preparsing would be necessary here!) Now, if I'm 
understanding this correctly, the PREPARE statement would need to have 
the data types of each of the parameters specified. Is this something 
that's done in other DBI drivers?

Okay, sorry for all the questions. My motivation is to make a new 
PostgreSQL DBI driver that's one of the best DBI drivers around. Any 
help would go a long way toward helping me to reach my goal.

TIA,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Sun, 17 Nov 2002 19:00:30 -0800
David Wheeler <david@wheeler.net>
DBD::PostgreSQL
This is great to hear ... possible name of PgXS? (not that the current
version isn't using XS), allows both Pg and the new Pg (along with PgSPI) to
be installed at once.

On Sun, Nov 17, 2002 at 07:00:30PM -0800, David Wheeler wrote:
> programmed C before; and c) I didn't want to just totally hork the 
> DBD::Pg sources, I took it upon myself to try creating a new PostgreSQL 
> driver from scratch.

Learning under fire, the best way!

> * In DBD::Pg's dbdimp.c, the dbd_db_commit() function attempts a 
> commit, and if it's successful, it then starts another transaction. Is 
> this the proper behavior? The other DBDs I looked at don't appear to 
> BEGIN a new transaction in the dbd_db_commit() function.

Yes, when AutoCommit is on, each statement is committed after execution.
DBD::ADO uses an ADO function that starts a new transaction after a successful
commit or rollback of the current.  It's switching between the two states that
gets difficult to handle (also supporting database types that do not support
transactions).

> * A similar question applies to dbd_db_rollback(). It does a rollback, 
> and then BEGINs a new transaction. Should it be starting another 
> transaction there?

Yes.

> * How is DBI's begin_work() method intended to influence commits and 
> rollbacks?

Info from the DBI doc:
        "begin_work" $rc = $dbh->begin_work or die $dbh->errstr;

        Enable transactions (by turning "AutoCommit" off) until the next call to
        "commit" or "rollback". After the next "commit" or "rollback",
        "AutoCommit" will automatically be turned on again.

        If "AutoCommit" is already off when "begin_work" is called then it does
        nothing except return an error. If the driver does not support
        transactions then when "begin_work" attempts to set "AutoCommit" off the
        driver will trigger a fatal error.

        See also "Transactions" in the "FURTHER INFORMATION" section below.

IMHO: begin_work for Pg simply turns AutoCommit off.  The AutoCommit handles
committing the current transaction and starting the next.

> * Also in dbd_db_commit() and dbd_db_rollback(), I notice that the last 
> return statement returns 0. Shouldn't these be returning true?

Success is non-zero. However, $dbh->err is 0 or undefined.

Info from DBI doc:
  "commit"
	$rc  = $dbh->commit     or die $dbh->errstr;


> * In DBD::Pg's dbdimp.c, the dbd_db_disconnect() function automatically 
> does a rollback if AutoCommit is off. Should there not be some way to 
> tell that, in addition to AutoCommit being off, a transaction is 
> actually in progress? That is to say, since the last call to 
> dbd_db_commit() that some statements have actually been executed? Or 
> does this matter?


IMHO: It's much safer to rollback (unconditionally) on disconnect, then
attempting to manage tracking the current action taken in the
transaction by the different statement handlers.

> * And finally, is dbd_preparse() totally necessary? I mean, doesn't 
> PostgreSQL's PQexec() function do the necessary parsing? Jeffrey Baker 
> mentioned to me that he was working on a new parser, and perhaps I'm 
> missing something (because of parameters?), but I'm just trying to 
> figure out why this is even necessary.

AFAIK:  All the drivers support dbd_preparse.

> * One more thing: I was looking at the PostgreSQL documents for the new 
> support for prepared statements in version 7.3. They look like this:
> 
> PREPARE q3(text, int, float, boolean, oid, smallint) AS
>       SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
>       ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
> 
> (BTW, I can see why preparsing would be necessary here!) Now, if I'm 
> understanding this correctly, the PREPARE statement would need to have 
> the data types of each of the parameters specified. Is this something 
> that's done in other DBI drivers?

Ouch ... that may make things ugly.  
It'll give you fewer nightmares if you can pass the "statement" to
the back-end to prepare, having the back-end return the number of
parameters, and data types. (I haven't looked at the 7.3 PostgreSQL
documentation yet). If the back-end doesn't support this type of
prepare, then you may need to pre-parse the statement to determine
what placeholders are requires and attempt to determine the correct
data types.

Tom

-- 
Thomas A. Lowery
See DBI/FAQ http://xmlproj.dyndns.org/cgi-bin/fom

Sun, 17 Nov 2002 23:21:22 -0500
"Thomas A. Lowery" <tl-lists@stlowery.net>
Re: DBD::PostgreSQL
On Sun, 17 Nov 2002, David Wheeler wrote:

> * In DBD::Pg's dbdimp.c, the dbd_db_commit() function attempts a 
> commit, and if it's successful, it then starts another transaction. Is 
> this the proper behavior? The other DBDs I looked at don't appear to 
> BEGIN a new transaction in the dbd_db_commit() function.
> 
> * A similar question applies to dbd_db_rollback(). It does a rollback, 
> and then BEGINs a new transaction. Should it be starting another 
> transaction there?
> 

Current behaviour sounds about right. Iff you are not in auto commit mode,
you have to tell pg to start a new transaction. IIRC, some DBs will
automatically start a new transaction when the commit/rollback is called;
however, for pg, an explicit BEGIN is required to start the transaction.

> * How is DBI's begin_work() method intended to influence commits and 
> rollbacks?
> 

I would guess this is along the lines of std PostgeSQL behaviour; when you
begin_work you tell the db to start a transaction (BEGIN) up until the
next commit/rollback.  So instead of turning autocommit off you can just
begin work around the blocks of code that need transactions.  (cf. local
($dbh->{AutoCommit}) = 0)


> * Also in dbd_db_commit() and dbd_db_rollback(), I notice that the last 
> return statement returns 0. Shouldn't these be returning true?

dbd_db_commit() returns zero when NULL == $imp_dbh->conn or on error. It 
returns one when when PGRES_COMMAND_OK == status.

Humm intersting... It look like the data can be committed to database &
dbd_db_commit can still through an error because the BEGIN failed.  Ugg.
This could be non-pretty.

all of the above also goes for  rollback().


> * In DBD::Pg's dbdimp.c, the dbd_db_disconnect() function automatically 
> does a rollback if AutoCommit is off. Should there not be some way to 
> tell that, in addition to AutoCommit being off, a transaction is 
> actually in progress? That is to say, since the last call to 
> dbd_db_commit() that some statements have actually been executed? Or 
> does this matter?
> 

A transaction is already in progress because you have called BEGIN.


> * In dbd_db_destroy(), if I'm using Driver.xst, I don't actually need 
> to execute this code, correct?
> 
>      if (DBIc_ACTIVE(imp_dbh)) {
>          dbd_db_disconnect(dbh, imp_dbh);
>      }
> 

Don't know, but it looks like (cursory glance) that dbd_db_disconnect gets 
called already before dbd_db_destory in DESTROY of Driver.xst. But hey 
can't hurt, right :)


> * And finally, is dbd_preparse() totally necessary? I mean, doesn't 
> PostgreSQL's PQexec() function do the necessary parsing? Jeffrey Baker 
> mentioned to me that he was working on a new parser, and perhaps I'm 
> missing something (because of parameters?), but I'm just trying to 
> figure out why this is even necessary.


dbd_preparse scans and rewrites the query for placeholders, so if you
want to use placeholders with prepare, you will need to walk the string 
looking for placeholders. How do you think DBD::Pg knows that when you 
say $sth = $x->prepare("SELECT * FROM thing WHERE 1=? and 2 =?) that $sth 
is going to need two placeholders when execute() is called?



> * One more thing: I was looking at the PostgreSQL documents for the new 
> support for prepared statements in version 7.3. They look like this:
> 
> PREPARE q3(text, int, float, boolean, oid, smallint) AS
> 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
> 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
> 

 From my rough scanning of the docs a few weeks ago, I think that the 
types are optional (I hope that thy are, in any event), & you are 
missing the plan_name. 

To get this to work automagically in DBD::Pg, you would have
dbd_st_reparse rewrite the placeholders ?/p:1/&c. as $1, $2, $4, &c, then
prepend a PREPARE plan_name, and then issue the query to the db
(remembering the plan name that you created for the call to execute
later).

> (BTW, I can see why preparsing would be necessary here!) Now, if I'm 
> understanding this correctly, the PREPARE statement would need to have 
> the data types of each of the parameters specified. Is this something 
> that's done in other DBI drivers?

You do not want to go there (trying to magically get the types for the 
placeholders (unless PostgreSQL will give them to you)).

Later,

-r


Sun, 17 Nov 2002 23:26:11 -0500 (EST)
Rudy Lippan <rlippan@remotelinux.com>
Re: DBD::PostgreSQL
David Wheeler <david@wheeler.net> writes:
> * In DBD::Pg's dbdimp.c, the dbd_db_commit() function attempts a 
> commit, and if it's successful, it then starts another transaction. Is 
> this the proper behavior? The other DBDs I looked at don't appear to 
> BEGIN a new transaction in the dbd_db_commit() function.
> * A similar question applies to dbd_db_rollback(). It does a rollback, 
> and then BEGINs a new transaction. Should it be starting another 
> transaction there?

Both of these seem pretty bogus to me.  Ideally the driver should not
issue a "begin" until the application issues the first command of the
new transaction.  Otherwise you get into scenarios where idle
connections are holding open transactions, and ain't nobody gonna be
happy with that.

> (BTW, I can see why preparsing would be necessary here!) Now, if I'm 
> understanding this correctly, the PREPARE statement would need to have 
> the data types of each of the parameters specified. Is this something 
> that's done in other DBI drivers?

Probably not --- the SQL spec seems to think that the server can intuit
appropriate datatypes for each parameter symbol.  (Which I suppose may
be true, in a datatype universe as impoverished as the spec's is;
but it won't work for Postgres.  Thus we have a nonstandard syntax for
PREPARE.)  So you'll probably have to do some driver-specific coding here.

No ideas about your other questions, but I hope the DBI folk can answer.

> Okay, sorry for all the questions. My motivation is to make a new 
> PostgreSQL DBI driver that's one of the best DBI drivers around. Any 
> help would go a long way toward helping me to reach my goal.

Go to it ;-)

			regards, tom lane

Mon, 18 Nov 2002 01:15:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DBD::PostgreSQL 
On Monday 18 November 2002 04:00, David Wheeler wrote:
> Dear Fellow DBI and PostgreSQL Hackers,

(...)
> Okay, sorry for all the questions. My motivation is to make a new
> PostgreSQL DBI driver that's one of the best DBI drivers around. Any
> help would go a long way toward helping me to reach my goal.

Count me in. I'm still in a slight state of shock after wondering over
to CPAN to find out how DBD::Pg was coming along ;-). At the
very least I can do testing and documentation, and quite possibly
"grunt work". Anything else will depend on how quickly I can
acquaint myself with the internals of DBI. (Note to self: do not believe
this is impossible or anything). Perl is my main 
development language, and I used to work a lot with C.

For clarification: is DBD::Postgres intended to replace DBD::Pg, and are
any maintenance releases of the latter planned (e.g. in conjunction with
the PostgreSQL 7.3. release)?

Ian Barwick
barwick@gmx.net


Mon, 18 Nov 2002 09:32:11 +0100
Ian Barwick <barwick@gmx.net>
Re: DBD::PostgreSQL
On Sun, Nov 17, 2002 at 07:00:30PM -0800, David Wheeler wrote:
> 
> * In DBD::Pg's dbdimp.c, the dbd_db_commit() function attempts a 
> commit, and if it's successful, it then starts another transaction. Is 
> this the proper behavior? The other DBDs I looked at don't appear to 
> BEGIN a new transaction in the dbd_db_commit() function.

Many databases, like Oracle, automatically start a transaction at
the server as soon as it's needed. The application doesn't have to
do it explicitly. (DBD::Informix is probably a good example of a
driver that needs to start transactions explicitly.)

> * A similar question applies to dbd_db_rollback(). It does a rollback, 
> and then BEGINs a new transaction. Should it be starting another 
> transaction there?

Drivers are free to defer starting a new transaction until it's needed.
Or they can start one right away, but that may cause problems on
the server if there are many 'idle transactions'. (Also beware that
some databases don't allow certain statements, like some 'alter
session ...', to be issued while a transaction is active. If that
applies to Pg then you may have a problem.)

> * How is DBI's begin_work() method intended to influence commits and 
> rollbacks?

 From the source:

    sub begin_work {
        my $dbh = shift;
        return $dbh->DBI::set_err(1, "Already in a transaction")
                unless $dbh->FETCH('AutoCommit');
        $dbh->STORE('AutoCommit', 0); # will croak if driver doesn't support it
        $dbh->STORE('BegunWork',  1); # trigger post commit/rollback action
    }

drivers do *not* need to define their own begin_work method.

What they _should_ do is make their commit and rollback methods
check for BegunWork being true (it's a bit flag in the com structure)
and if true then turn AutoCommit back on instead of starting a new transaction.

(If they don't do that then the DBI handles it but it's faster,
cleaner, and safer for teh driver to do it.)


> * Also in dbd_db_commit() and dbd_db_rollback(), I notice that the last 
> return statement returns 0. Shouldn't these be returning true?

Yes, when using Driver.xst, if there's no error.

> Okay, sorry for all the questions. My motivation is to make a new 
> PostgreSQL DBI driver that's one of the best DBI drivers around. Any 
> help would go a long way toward helping me to reach my goal.

I'd really appreciate any feedback (ie patches :) you might have
for the DBI::DBD document.  It's a bit thin and/or dated in places.

Tim.



Mon, 18 Nov 2002 10:15:55 +0000
Tim Bunce <Tim.Bunce@pobox.com>
Re: DBD::PostgreSQL
On Mon, Nov 18, 2002 at 10:15:55AM +0000, Tim Bunce wrote:
> 
> What they _should_ do is make their commit and rollback methods
> check for BegunWork being true (it's a bit flag in the com structure)
> and if true then turn AutoCommit back on instead of starting a new transaction.

(and turn BegunWork back off.)

Tim.

Mon, 18 Nov 2002 10:26:20 +0000
Tim Bunce <Tim.Bunce@pobox.com>
Re: DBD::PostgreSQL
On Mon, 18 Nov 2002, Ian Barwick wrote:

> For clarification: is DBD::Postgres intended to replace DBD::Pg, and are
> any maintenance releases of the latter planned (e.g. in conjunction with
> the PostgreSQL 7.3. release)?

I didn't see any indication that David's planning on giving a new name to
his rewritten PostgreSQL DBD driver, other than the subject of his email.
It would cause a lot of pain if the driver's name changed from DBD::Pg,
since every place people have DSNs in code or config files would have to
be updated ...

Would anyone actually consider using a different name than DBD::Pg? It
seems it would be a big pain with no benefit, and make it unclear which 
driver users should use.

Jon


Mon, 18 Nov 2002 14:47:56 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: DBD::PostgreSQL
On Sunday, November 17, 2002, at 10:15  PM, Tom Lane wrote:

> Both of these seem pretty bogus to me.  Ideally the driver should not
> issue a "begin" until the application issues the first command of the
> new transaction.  Otherwise you get into scenarios where idle
> connections are holding open transactions, and ain't nobody gonna be
> happy with that.

Okay. I think I'll use a flag in the driver to track when it's in a 
transaction, and do the right thing in the begin and rollback functions.

> Probably not --- the SQL spec seems to think that the server can intuit
> appropriate datatypes for each parameter symbol.  (Which I suppose may
> be true, in a datatype universe as impoverished as the spec's is;
> but it won't work for Postgres.  Thus we have a nonstandard syntax for
> PREPARE.)  So you'll probably have to do some driver-specific coding 
> here.

So, if I understand you correctly, PostgreSQL's PREPARE statement 
*requires* data typing in its syntax? If so, is there an 
easy/straight-forward way to ask the server what the data types for 
each column are before executing the PREPARE?

> No ideas about your other questions, but I hope the DBI folk can 
> answer.

Thanks, yes, I'm getting some good responses.

> Go to it ;-)

Thanks Tom!

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:16:03 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL 
David Wheeler <david@wheeler.net> writes:
> On Sunday, November 17, 2002, at 10:15  PM, Tom Lane wrote:
>> Both of these seem pretty bogus to me.  Ideally the driver should not
>> issue a "begin" until the application issues the first command of the
>> new transaction.  Otherwise you get into scenarios where idle
>> connections are holding open transactions, and ain't nobody gonna be
>> happy with that.

> Okay. I think I'll use a flag in the driver to track when it's in a 
> transaction, and do the right thing in the begin and rollback functions.

I think someone else said that the DBD framework already includes such a
flag ("BegunWork"?) --- if so, you should surely use that one.

> So, if I understand you correctly, PostgreSQL's PREPARE statement 
> *requires* data typing in its syntax?

Yup.

> If so, is there an 
> easy/straight-forward way to ask the server what the data types for 
> each column are before executing the PREPARE?

There are various ways to retrieve the datatypes of the columns of a
table, but I'm not sure how that helps you to determine the parameter
types for an arbitrary SQL command to be prepared.  Are you assuming
a specific structure of the command you want to prepare?

			regards, tom lane

Mon, 18 Nov 2002 11:19:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DBD::PostgreSQL 
On Monday, November 18, 2002, at 08:19  AM, Tom Lane wrote:

> I think someone else said that the DBD framework already includes such 
> a
> flag ("BegunWork"?) --- if so, you should surely use that one.

No, I'm finding out that that flag is for a slightly different purpose 
-- using transactions even when AutoCommit = 1.

>> So, if I understand you correctly, PostgreSQL's PREPARE statement
>> *requires* data typing in its syntax?
>
> Yup.

Damn.

> There are various ways to retrieve the datatypes of the columns of a
> table, but I'm not sure how that helps you to determine the parameter
> types for an arbitrary SQL command to be prepared.  Are you assuming
> a specific structure of the command you want to prepare?

Ouch, good point. I don't want to go there. It's a shame, really, but 
in light of this requirement, I don't see how PostgreSQL prepared 
statements can be supported by the DBI. Pity; I was really looking 
forward to the performance boost.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:27:33 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL 
On Mon, Nov 18, 2002 at 11:19:25AM -0500, Tom Lane wrote:
> David Wheeler <david@wheeler.net> writes:
> > On Sunday, November 17, 2002, at 10:15  PM, Tom Lane wrote:
> >> Both of these seem pretty bogus to me.  Ideally the driver should not
> >> issue a "begin" until the application issues the first command of the
> >> new transaction.  Otherwise you get into scenarios where idle
> >> connections are holding open transactions, and ain't nobody gonna be
> >> happy with that.
> 
> > Okay. I think I'll use a flag in the driver to track when it's in a 
> > transaction, and do the right thing in the begin and rollback functions.
> 
> I think someone else said that the DBD framework already includes such a
> flag ("BegunWork"?) --- if so, you should surely use that one.

BegunWork _only_ relates to the begin_work method. It's not used unless
that method is used, so it's not appropriate for your use here.

Just add a flag to the drivers private structure.

Tim.

Mon, 18 Nov 2002 16:39:08 +0000
Tim Bunce <Tim.Bunce@pobox.com>
Re: DBD::PostgreSQL
On Sunday, November 17, 2002, at 08:26  PM, Rudy Lippan wrote:

> Current behaviour sounds about right. Iff you are not in auto commit 
> mode,
> you have to tell pg to start a new transaction. IIRC, some DBs will
> automatically start a new transaction when the commit/rollback is 
> called;
> however, for pg, an explicit BEGIN is required to start the 
> transaction.

With feedback from Tom Lane, I think I'll add code to track when to 
BEGIN a transaction, and check it in execute() to see if it needs to be 
turned on before executing a statement.

> I would guess this is along the lines of std PostgeSQL behaviour; when 
> you
> begin_work you tell the db to start a transaction (BEGIN) up until the
> next commit/rollback.  So instead of turning autocommit off you can 
> just
> begin work around the blocks of code that need transactions.  (cf. 
> local
> ($dbh->{AutoCommit}) = 0)

Okay, so if I understand correctly, it's an alternative to AutoCommit 
for handling transactions. That explains why they *both* need to be 
checked.

> dbd_db_commit() returns zero when NULL == $imp_dbh->conn or on error. 
> It
> returns one when when PGRES_COMMAND_OK == status.

Okay.

> Humm intersting... It look like the data can be committed to database &
> dbd_db_commit can still through an error because the BEGIN failed.  
> Ugg.
> This could be non-pretty.

Yeah, that's another reason to set a flag and remove the BEGIN from 
dbd_db_commit() and dbd_db_rollback().

> A transaction is already in progress because you have called BEGIN.

Yes, but if I set the flag as I've mentioned above, I may not have. It 
makes sense to me to use the init_commit flag for this purpose.

> Don't know, but it looks like (cursory glance) that dbd_db_disconnect 
> gets
> called already before dbd_db_destory in DESTROY of Driver.xst. But hey
> can't hurt, right :)

Um, yes, I guess that's true. I was thinking about redundant operations 
using more time, but I guess that doesn't really matter in 
dbd_db_destroy() (and it takes next to no time, anyway).

> dbd_preparse scans and rewrites the query for placeholders, so if you
> want to use placeholders with prepare, you will need to walk the string
> looking for placeholders. How do you think DBD::Pg knows that when you
> say $sth = $x->prepare("SELECT * FROM thing WHERE 1=? and 2 =?) that 
> $sth
> is going to need two placeholders when execute() is called?

Right, okay, that's *kind of* what I thought. It just seems a shame 
that each query has to be parsed twice (once by the DBI driver, once by 
PostgreSQL). But I guess there's no other way about it. Perhaps our 
preparsed statement could be cached by prepare_cached(), so that, even 
though we can't cache a statement prepared by PostgreSQL (see my 
exchange with Tom Lane), we could at least cache our own parsed 
statement.

>> * One more thing: I was looking at the PostgreSQL documents for the 
>> new
>> support for prepared statements in version 7.3. They look like this:
>>
>> PREPARE q3(text, int, float, boolean, oid, smallint) AS
>> 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
>> 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
>>
> From my rough scanning of the docs a few weeks ago, I think that the
> types are optional (I hope that thy are, in any event), & you are
> missing the plan_name.

Unfortunately, according to Tom Lane, the data types are required. :-(
FWIW with the above example, I swiped it right out of PostgreSQL's 
tests. the plan_name is "q3".

> You do not want to go there (trying to magically get the types for the
> placeholders (unless PostgreSQL will give them to you)).

Not easily, I think. A shame, really, that the data types are required, 
as it means that dynamic database clients like DBI (and, I expect, 
JDBC) won't really be able to take advantage of prepared statements. 
Only custom code that uses the PostgreSQL API directly (that is, C 
applications) will be able to do it.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:42:01 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 08:39  AM, Tim Bunce wrote:

> BegunWork _only_ relates to the begin_work method. It's not used unless
> that method is used, so it's not appropriate for your use here.
>
> Just add a flag to the drivers private structure.

Right, that's my plan.

Thanks Tim!

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:44:52 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 12:32  AM, Ian Barwick wrote:

> Count me in. I'm still in a slight state of shock after wondering over
> to CPAN to find out how DBD::Pg was coming along ;-). At the
> very least I can do testing and documentation, and quite possibly
> "grunt work". Anything else will depend on how quickly I can
> acquaint myself with the internals of DBI. (Note to self: do not 
> believe
> this is impossible or anything). Perl is my main
> development language, and I used to work a lot with C.

Well then, once I finish pasting together dbdimp.c and get everything 
to compile, I might ask for your help with a code review and writing 
tests.

> For clarification: is DBD::Postgres intended to replace DBD::Pg, and 
> are
> any maintenance releases of the latter planned (e.g. in conjunction 
> with
> the PostgreSQL 7.3. release)?

And on Monday, November 18, 2002, at 06:47  AM, Jon Jensen wrote:

> I didn't see any indication that David's planning on giving a new name 
> to
> his rewritten PostgreSQL DBD driver, other than the subject of his 
> email.
> It would cause a lot of pain if the driver's name changed from DBD::Pg,
> since every place people have DSNs in code or config files would have 
> to
> be updated ...
>
> Would anyone actually consider using a different name than DBD::Pg? It
> seems it would be a big pain with no benefit, and make it unclear which
> driver users should use.

I expect that the PostgreSQL developers will include whatever DBI 
driver is the "official" DBI driver for PostgreSQL. At this point, I've 
just changed the name so I can feel free to hack it any way I like, 
including breaking backward compatibility where necessary (such as in 
the escape() method).

If I finish something that actually works, then I'll request some help 
from others comparing it to the behavior of the DBD::Pg driver. If it 
doesn't break backwards compatibility too much, then I would suggest 
that it become DBD::Pg 1.20 or 2.0 or something. But if its behavior is 
different enough (and it would need to be tried with a number of 
different applications to see what breaks, I think), then it would 
probably have to be released under a different name and people could 
switch if/when they could. But we're a long ways from determining that 
just yet.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:49:17 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 02:15  AM, Tim Bunce wrote:

> Many databases, like Oracle, automatically start a transaction at
> the server as soon as it's needed. The application doesn't have to
> do it explicitly. (DBD::Informix is probably a good example of a
> driver that needs to start transactions explicitly.)

I'm quite sure that in PostgreSQL, transactions have to be started 
explicitly.

> Drivers are free to defer starting a new transaction until it's needed.
> Or they can start one right away, but that may cause problems on
> the server if there are many 'idle transactions'. (Also beware that
> some databases don't allow certain statements, like some 'alter
> session ...', to be issued while a transaction is active. If that
> applies to Pg then you may have a problem.)

According to Tom Lane, idle transactions are problematic, so I think 
I'll code it up to start the transaction when its needed -- presumably 
by checking and setting the relevant flags in execute().

> drivers do *not* need to define their own begin_work method.
>
> What they _should_ do is make their commit and rollback methods
> check for BegunWork being true (it's a bit flag in the com structure)
> and if true then turn AutoCommit back on instead of starting a new 
> transaction.
>
> (If they don't do that then the DBI handles it but it's faster,
> cleaner, and safer for teh driver to do it.)

Okay, then that's what I'll do. Do I check it like this?

   if (DBIc_has(imp_dbh, DBIcf_BegunWork)) {...}

>> * Also in dbd_db_commit() and dbd_db_rollback(), I notice that the 
>> last
>> return statement returns 0. Shouldn't these be returning true?
>
> Yes, when using Driver.xst, if there's no error.

It appears that they return false when imp_dbh->conn is NULL. That 
would count as an error, I think. DBD::Pg doesn't report it as an 
error, though -- it just returns false. Should I add an appropriate 
call to do_error() in such a case?

>> Okay, sorry for all the questions. My motivation is to make a new
>> PostgreSQL DBI driver that's one of the best DBI drivers around. Any
>> help would go a long way toward helping me to reach my goal.
>
> I'd really appreciate any feedback (ie patches :) you might have
> for the DBI::DBD document.  It's a bit thin and/or dated in places.

Yes, I've thought about that. You can at least expect a bit of clean up 
(grammar, etc.), but I might well add more. It'd probably be good to do 
so as a newbie who wants to help other newbies along...

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:55:20 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 02:26  AM, Tim Bunce wrote:

>> What they _should_ do is make their commit and rollback methods
>> check for BegunWork being true (it's a bit flag in the com structure)
>> and if true then turn AutoCommit back on instead of starting a new 
>> transaction.
>
> (and turn BegunWork back off.)

Gotcha. Thanks.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 08:55:34 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
David Wheeler <david@wheeler.net> writes:
> On Monday, November 18, 2002, at 08:19  AM, Tom Lane wrote:
>> There are various ways to retrieve the datatypes of the columns of a
>> table, but I'm not sure how that helps you to determine the parameter
>> types for an arbitrary SQL command to be prepared.  Are you assuming
>> a specific structure of the command you want to prepare?

> Ouch, good point. I don't want to go there. It's a shame, really, but 
> in light of this requirement, I don't see how PostgreSQL prepared 
> statements can be supported by the DBI. Pity; I was really looking 
> forward to the performance boost.

Thinking about this, it occurs to me that there's no good reason why
we couldn't allow parameter symbols ($n) to be considered type UNKNOWN
initially.  The type interpretation algorithms would then treat them
just like quoted literal constants.  After parsing finishes, PREPARE
could scan the tree to see what type each symbol had been cast to.
(You'd have to raise an error if multiple appearances of the same symbol
had been cast to different types, but that'd be an uncommon case.)

This form of PREPARE would presumably need some way of reporting back
the types it had determined for the symbols; anyone have a feeling for
the appropriate API for that?

			regards, tom lane

Mon, 18 Nov 2002 11:58:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
On Sunday, November 17, 2002, at 08:21  PM, Thomas A. Lowery wrote:

> This is great to hear ... possible name of PgXS? (not that the current
> version isn't using XS), allows both Pg and the new Pg (along with 
> PgSPI) to
> be installed at once.

Well, if the name needs to change, I was thinking of DBD::PgSQL. Is 
someone working on DBD::PgSPI? That might be even more valuable, since 
that appears to be a much more robust API.

> Learning under fire, the best way!

Yes...or I'm a crazy bastard. Take your pick.

> Yes, when AutoCommit is on, each statement is committed after 
> execution.
> DBD::ADO uses an ADO function that starts a new transaction after a 
> successful
> commit or rollback of the current.  It's switching between the two 
> states that
> gets difficult to handle (also supporting database types that do not 
> support
> transactions).

So in DBD::ADO, you're not actually deferring starting a new 
transaction until it's actually needed? Are there no problems with idle 
transactions?

> IMHO: begin_work for Pg simply turns AutoCommit off.  The AutoCommit 
> handles
> committing the current transaction and starting the next.

Okay.

>> * Also in dbd_db_commit() and dbd_db_rollback(), I notice that the 
>> last
>> return statement returns 0. Shouldn't these be returning true?
>
> Success is non-zero. However, $dbh->err is 0 or undefined.
>
> Info from DBI doc:
>   "commit"
> 	$rc  = $dbh->commit     or die $dbh->errstr;

Yes. However, dbd_db_commit() and dbd_db_rollback() can return false 
without throwing an error. I think that's a mistake.

> IMHO: It's much safer to rollback (unconditionally) on disconnect, then
> attempting to manage tracking the current action taken in the
> transaction by the different statement handlers.

Don't all statement ultimately go through dbd_st_execute()? If so, then 
I think it'd be relatively easy to just start the transaction when its 
needed, and then dbd_db_disconnect() can check for a flag indicating 
whether a transaction is actually in progress or not.

> AFAIK:  All the drivers support dbd_preparse.

Okay, got it.

> Ouch ... that may make things ugly.

Amen.

> It'll give you fewer nightmares if you can pass the "statement" to
> the back-end to prepare, having the back-end return the number of
> parameters, and data types. (I haven't looked at the 7.3 PostgreSQL
> documentation yet). If the back-end doesn't support this type of
> prepare, then you may need to pre-parse the statement to determine
> what placeholders are requires and attempt to determine the correct
> data types.

AFAIK, there currently is no API for this, but I think that this 
exchange might have tickled some ideas among the PostgreSQL 
developers... :-)

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 09:06:30 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
David Wheeler <david@wheeler.net> writes:
> I'm quite sure that in PostgreSQL, transactions have to be started 
> explicitly.

As of 7.3 that's not necessarily so anymore; you can "SET autocommit TO
off" and get the behavior where any statement starts a transaction block
(and so an explicit COMMIT is required to commit its effects).  Not
sure if this helps you or not.

			regards, tom lane

Mon, 18 Nov 2002 12:12:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DBD::PostgreSQL 
On Monday, November 18, 2002, at 08:58  AM, Tom Lane wrote:

> Thinking about this, it occurs to me that there's no good reason why
> we couldn't allow parameter symbols ($n) to be considered type UNKNOWN
> initially.  The type interpretation algorithms would then treat them
> just like quoted literal constants.  After parsing finishes, PREPARE
> could scan the tree to see what type each symbol had been cast to.
> (You'd have to raise an error if multiple appearances of the same 
> symbol
> had been cast to different types, but that'd be an uncommon case.)
>
> This form of PREPARE would presumably need some way of reporting back
> the types it had determined for the symbols; anyone have a feeling for
> the appropriate API for that?

If I'm understanding you correctly this approach would make it much 
easier on dynamic drivers such as DBI and JDBC. Ideally, in DBI, I'd be 
able to do something like this:

PREPARE my_stmt AS
     SELECT foo, bar
     FROM   bat
     WHERE  foo = $1
            AND bar = $2;

EXECUTE my_stmt('foo_val', 'bar_val');

It would be the responsibility of the PostgreSQL PREPARE parser to 
handle the data typing of $1 and $2, and the responsibility of the DBI 
client to pass in data of the appropriate type.

Is this along the lines of what you're thinking, Tom?

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 09:15:16 -0800
David Wheeler <david@wheeler.net>
Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
On Monday, November 18, 2002, at 09:12  AM, Tom Lane wrote:

> As of 7.3 that's not necessarily so anymore; you can "SET autocommit TO
> off" and get the behavior where any statement starts a transaction 
> block
> (and so an explicit COMMIT is required to commit its effects).  Not
> sure if this helps you or not.

PostgreSQL gets better and better. Yay. However, although I might be 
able to use compile-time macros to determine the PostgreSQL version, I 
have to support a minimum version of PostsgreSQL in the driver. I was 
thinking 7.0 -- maybe it's time to leave the 6.x series behind.

Thoughts, DBD::Pg users?

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 09:17:05 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL 
On Mon, Nov 18, 2002 at 08:55:20AM -0800, David Wheeler wrote:
> On Monday, November 18, 2002, at 02:15  AM, Tim Bunce wrote:
> 
> Okay, then that's what I'll do. Do I check it like this?
> 
>   if (DBIc_has(imp_dbh, DBIcf_BegunWork)) {...}

Yeap.

> >>* Also in dbd_db_commit() and dbd_db_rollback(), I notice that the 
> >>last
> >>return statement returns 0. Shouldn't these be returning true?
> >
> >Yes, when using Driver.xst, if there's no error.
> 
> It appears that they return false when imp_dbh->conn is NULL. That 
> would count as an error, I think. DBD::Pg doesn't report it as an 
> error, though -- it just returns false. Should I add an appropriate 
> call to do_error() in such a case?

Probably. It's fairly important that a method doesn't return an error
status without having recorded the error by at least doing
	sv_setiv(DBIc_ERR(imp_xxh), ...)

> >I'd really appreciate any feedback (ie patches :) you might have
> >for the DBI::DBD document.  It's a bit thin and/or dated in places.
> 
> Yes, I've thought about that. You can at least expect a bit of clean up 
> (grammar, etc.), but I might well add more. It'd probably be good to do 
> so as a newbie who wants to help other newbies along...

Great. Thanks.

Tim.

Mon, 18 Nov 2002 17:33:56 +0000
Tim Bunce <Tim.Bunce@pobox.com>
Re: DBD::PostgreSQL
On Monday 18 November 2002 18:17, David Wheeler wrote:

> PostgreSQL gets better and better. Yay. However, although I might be
> able to use compile-time macros to determine the PostgreSQL version, I
> have to support a minimum version of PostsgreSQL in the driver. I was
> thinking 7.0 -- maybe it's time to leave the 6.x series behind.
>
> Thoughts, DBD::Pg users?

Existing versions of DBD::Pg aren't suddenly going to get broken ;-)
The README for DBD::Pg 1.13 says 6.5 is the minimum required version,
which I believe was the last major release before 7.0. So anyone
using < 6.5 is out of the DBD::Pg upgrade cycle anyway. A README-
note along the lines of "PostgreSQL 6.5.x users: you need DBD::Pg 1.13"
should suffice.

Ian Barwick
barwick@gmx.net



Mon, 18 Nov 2002 18:36:02 +0100
Ian Barwick <barwick@akademie.de>
Re: DBD::PostgreSQL
David Wheeler <david@wheeler.net> writes:
> PostgreSQL gets better and better. Yay. However, although I might be 
> able to use compile-time macros to determine the PostgreSQL version, I 
> have to support a minimum version of PostsgreSQL in the driver. I was 
> thinking 7.0 -- maybe it's time to leave the 6.x series behind.

It's way past time to forget 6.* ;-).  Based on what we see in the
mailing lists, hardly anyone is on 7.0.* either, and the people on
7.1.* all know they need to upgrade.

For a newly coded DBD driver, I think you could get away with setting a
baseline requirement of a 7.2 server.  Maybe even 7.3, if you wanted to
be a hard case (and you aren't planning to release for a few months).

			regards, tom lane

Mon, 18 Nov 2002 12:39:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DBD::PostgreSQL 
On Mon, Nov 18, 2002 at 08:42:01AM -0800, David Wheeler wrote:
> On Sunday, November 17, 2002, at 08:26  PM, Rudy Lippan wrote:
> 
> >I would guess this is along the lines of std PostgeSQL behaviour; when 
> >you
> >begin_work you tell the db to start a transaction (BEGIN) up until the
> >next commit/rollback.  So instead of turning autocommit off you can 
> >just
> >begin work around the blocks of code that need transactions.  (cf. 
> >local
> >($dbh->{AutoCommit}) = 0)
> 
> Okay, so if I understand correctly, it's an alternative to AutoCommit 
> for handling transactions. That explains why they *both* need to be checked.

Just to be sure this is clear, begin_work is *just* an alternative
way to set AutoCommit off till the next commit or rollback. The
application is free to just set AutoCommit explicitly as needed.

The *only* time a driver needs to consider the BegunWork attribute
is immediately after a commit or rollback.

> >dbd_preparse scans and rewrites the query for placeholders, so if you
> >want to use placeholders with prepare, you will need to walk the string
> >looking for placeholders. How do you think DBD::Pg knows that when you
> >say $sth = $x->prepare("SELECT * FROM thing WHERE 1=? and 2 =?) that 
> >$sth
> >is going to need two placeholders when execute() is called?
> 
> Right, okay, that's *kind of* what I thought. It just seems a shame 
> that each query has to be parsed twice (once by the DBI driver, once by 
> PostgreSQL).

Should need to "parse" in the formal sense, no full grammar is
needed, it's just a very quick skim through the string. If done in
C it should be too cheap to worry about. Especially as it's only
done at prepare() time, not execute().

> >>* One more thing: I was looking at the PostgreSQL documents for the 
> >>new
> >>support for prepared statements in version 7.3. They look like this:
> >>
> >>PREPARE q3(text, int, float, boolean, oid, smallint) AS
> >>	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
> >>	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
> >>
> >From my rough scanning of the docs a few weeks ago, I think that the
> >types are optional (I hope that thy are, in any event), & you are
> >missing the plan_name.
> 
> Unfortunately, according to Tom Lane, the data types are required. :-(
> FWIW with the above example, I swiped it right out of PostgreSQL's 
> tests. the plan_name is "q3".
> 
> >You do not want to go there (trying to magically get the types for the
> >placeholders (unless PostgreSQL will give them to you)).
> 
> Not easily, I think. A shame, really, that the data types are required, 
> as it means that dynamic database clients like DBI (and, I expect, 
> JDBC) won't really be able to take advantage of prepared statements. 
> Only custom code that uses the PostgreSQL API directly (that is, C 
> applications) will be able to do it.

Would binding a string type to an integer (etc) placeholder work?
If so, just ignore the types and bind everything as strings.
That's exactly what DBD::Oracle does.

Tim.

Mon, 18 Nov 2002 17:44:08 +0000
Tim Bunce <Tim.Bunce@pobox.com>
Re: DBD::PostgreSQL
On Mon, 18 Nov 2002, David Wheeler wrote:

> With feedback from Tom Lane, I think I'll add code to track when to 
> BEGIN a transaction, and check it in execute() to see if it needs to be 
> turned on before executing a statement.
> 

I thought about that, but I was thinking that it would add quite a bit to 
the complexity of the code (simpler is better all things being equal).  

The problem with waiting until the fist stmt is that DBD::Pg also makes 
requests to the DB behind the scenes, so now you have to keep track of tx 
status before quering the db and turn it off and turn it back on when done 
iff you are not already in tx....  

If you are going to say that transactions are only BEGINed when the user
executes statement then it would be a bug for the driver to just decide to
start one on the user.

I don't know what would be worse: Always having a transaction open when I 
am not in automcommit mode, and knowing about it, or having to worry 
whether I am in transaction because of some stmt that I or some other 
module issued (Including DBD::Pg).


> Um, yes, I guess that's true. I was thinking about redundant operations 
> using more time, but I guess that doesn't really matter in 
> dbd_db_destroy() (and it takes next to no time, anyway).

I look at it this way... make it simple and bullet-proof and then optimize 
if needed.


> > dbd_preparse scans and rewrites the query for placeholders, so if you
> > want to use placeholders with prepare, you will need to walk the string
> > looking for placeholders. How do you think DBD::Pg knows that when you
> > say $sth = $x->prepare("SELECT * FROM thing WHERE 1=? and 2 =?) that 
> > $sth
> > is going to need two placeholders when execute() is called?
> 
> Right, okay, that's *kind of* what I thought. It just seems a shame 
> that each query has to be parsed twice (once by the DBI driver, once by 
> PostgreSQL). But I guess there's no other way about it. Perhaps our 
> preparsed statement could be cached by prepare_cached(), so that, even 
> though we can't cache a statement prepared by PostgreSQL (see my 
> exchange with Tom Lane), we could at least cache our own parsed 
> statement.

Is that not what prepare_cached is for? One should only be preping 
the statement once anyway, right?

And the statement gets parsed twice by DBD::Pg. One time in prepare and 
One time in execute (for the substitution of parameters).

> 
> 
> Unfortunately, according to Tom Lane, the data types are required. :-(
> FWIW with the above example, I swiped it right out of PostgreSQL's 
> tests. the plan_name is "q3".

Missed the q3. 

PREPARE plan_name [ (datatype [, ...] ) ] AS  query

I guess I read that as (datatype) being optional... I guess it is only 
optional if there are no $1 &c. in the query, then.


Later,

-r


Mon, 18 Nov 2002 12:55:08 -0500 (EST)
Rudy Lippan <rlippan@remotelinux.com>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 09:44  AM, Tim Bunce wrote:

> Just to be sure this is clear, begin_work is *just* an alternative
> way to set AutoCommit off till the next commit or rollback. The
> application is free to just set AutoCommit explicitly as needed.
>
> The *only* time a driver needs to consider the BegunWork attribute
> is immediately after a commit or rollback.

Okay, thanks for the clarification, Tim.

> Should need to "parse" in the formal sense, no full grammar is
> needed, it's just a very quick skim through the string. If done in
> C it should be too cheap to worry about. Especially as it's only
> done at prepare() time, not execute().

Okay. I'm used to thinking about things in Perl time, so I'm not quite 
sure about the expense of the c code stuff. Thanks for the tip.

> Would binding a string type to an integer (etc) placeholder work?
> If so, just ignore the types and bind everything as strings.
> That's exactly what DBD::Oracle does.

Well, it wouldn't work for bytea columns, but that's true with 
non-prepared statements already, anyway. Interesting idea, and I think 
you may well be right.

Tom, can you confirm?

Thanks,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 10:03:54 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 09:55  AM, Rudy Lippan wrote:

> I don't know what would be worse: Always having a transaction open 
> when I
> am not in automcommit mode, and knowing about it, or having to worry
> whether I am in transaction because of some stmt that I or some other
> module issued (Including DBD::Pg).

I think that we could probably prevent the driver's statements from 
interfering with that, particularly if they're performed in dbdimp.c 
rather than in Pg.pm.

> I look at it this way... make it simple and bullet-proof and then 
> optimize
> if needed.

Right.

> Is that not what prepare_cached is for? One should only be preping
> the statement once anyway, right?

Right. I wasn't sure if that was already happening or not -- I haven't 
got that far in the code yet. :-)

> And the statement gets parsed twice by DBD::Pg. One time in prepare and
> One time in execute (for the substitution of parameters).

Right, although if we can add support for true prepared statements, we 
could eliminate the second parsing.

> Missed the q3.
>
> PREPARE plan_name [ (datatype [, ...] ) ] AS  query
>
> I guess I read that as (datatype) being optional... I guess it is only
> optional if there are no $1 &c. in the query, then.

Right, unfortunately true -- for now, anyway.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 10:07:06 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 09:33  AM, Tim Bunce wrote:

>> Okay, then that's what I'll do. Do I check it like this?
>>
>>   if (DBIc_has(imp_dbh, DBIcf_BegunWork)) {...}
>
> Yeap.

Great, thanks. I think I'll set the minimum DBI requirement to 1.20 in 
order to properly support this feature. Any objections, DBIers?

>> would count as an error, I think. DBD::Pg doesn't report it as an
>> error, though -- it just returns false. Should I add an appropriate
>> call to do_error() in such a case?
>
> Probably. It's fairly important that a method doesn't return an error
> status without having recorded the error by at least doing
> 	sv_setiv(DBIc_ERR(imp_xxh), ...)

I'll add the call, then.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 10:09:07 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Monday, November 18, 2002, at 09:39  AM, Tom Lane wrote:

> For a newly coded DBD driver, I think you could get away with setting a
> baseline requirement of a 7.2 server.  Maybe even 7.3, if you wanted to
> be a hard case (and you aren't planning to release for a few months).

I think it'll be a couple of months at least, yes.

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Mon, 18 Nov 2002 10:09:37 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL 
On Mon, 18 Nov 2002 09:17:05 -0800, David Wheeler wrote:
> On Monday, November 18, 2002, at 09:12 AM, Tom Lane wrote:
> > As of 7.3 that's not necessarily so anymore; you can "SET autocommit TO
> > off" and get the behavior where any statement starts a transaction block
> > (and so an explicit COMMIT is required to commit its effects). Not sure
> > if this helps you or not.
>
> PostgreSQL gets better and better. Yay. However, although I might be able
> to use compile-time macros to determine the PostgreSQL version, I have to
> support a minimum version of PostsgreSQL in the driver.

You'd also need a runtime check that the server you connected to was of a
sufficiently high version. I realise it would make things more complicated,
but would it be possible to keep the manual transaction starting behaviour,
as well as adding the 7.3 behaviour, and deciding which to do based on the
server version?

Also, I'd like booleans to be returned as 't'/'f', rather than 1/0, to match
the behaviour of other drivers. Or at least have a driver-specific flag to
control which values get used.

-- 
	Peter Haworth	pmh@edison.ioppublishing.com
"To vacillate or not to vacillate, that is the question ... or is it?"

Tue, 19 Nov 2002 12:19:48 +0000
"Peter Haworth" <pmh@edison.ioppublishing.com>
Re: DBD::PostgreSQL
On Tuesday, November 19, 2002, at 04:19  AM, Peter Haworth wrote:

> You'd also need a runtime check that the server you connected to was 
> of a
> sufficiently high version. I realise it would make things more 
> complicated,
> but would it be possible to keep the manual transaction starting 
> behaviour,
> as well as adding the 7.3 behaviour, and deciding which to do based on 
> the
> server version?

I think I'd rather do it at compile-time, depending on the PosgreSQL 
libraries available. Folks who compile against 7.3 and then connect to 
7.2 get what they ask for, IMO.

> Also, I'd like booleans to be returned as 't'/'f', rather than 1/0, to 
> match
> the behaviour of other drivers. Or at least have a driver-specific 
> flag to
> control which values get used.

I might add this as an option later -- it'd probably be fairly easy -- 
but right now I want to get core functionality nailed down, and since 
the 1/0 behavior is most Perlish, I'll stick to that as the default.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Tue, 19 Nov 2002 15:13:49 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Thinking about this, it occurs to me that there's no good reason why
> we couldn't allow parameter symbols ($n) to be considered type UNKNOWN
> initially.

Good idea.

> This form of PREPARE would presumably need some way of reporting back
> the types it had determined for the symbols; anyone have a feeling for
> the appropriate API for that?

Why would this be needed? Couldn't we rely on the client programmer to
know that '$n is of type foo', and then pass the appropriately-typed
data to EXECUTE?

If we *do* need an API for this, ISTM that by adding protocol-level
support for PREPARE/EXECUTE, this shouldn't be too difficult to do
(and analogous to the way we pass back type information for SELECT
results). It would also allow us to side-step the parser for EXECUTE
parameters, which was something that a few people had requested
earlier.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


19 Nov 2002 23:42:57 -0500
Neil Conway <neilc@samurai.com>
Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
Neil Conway <neilc@samurai.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> This form of PREPARE would presumably need some way of reporting back
>> the types it had determined for the symbols; anyone have a feeling for
>> the appropriate API for that?

> Why would this be needed? Couldn't we rely on the client programmer to
> know that '$n is of type foo', and then pass the appropriately-typed
> data to EXECUTE?

I don't think so.  You may as well ask why we waste bandwidth on passing
back info about the column names and types of a SELECT result ---
shouldn't the client know that already?  There are lots of middleware
layers that don't know it, or at least don't want to expend a lot of
code on trying to deduce it.

> If we *do* need an API for this, ISTM that by adding protocol-level
> support for PREPARE/EXECUTE, this shouldn't be too difficult to do
> (and analogous to the way we pass back type information for SELECT
> results).

I'm not sure what you mean by protocol-level support, but the one idea
I had was to return a table (equivalent to a SELECT result) listing
the parameter types.  This would not break libpq, for example, so
arguably it's not a protocol change.  But if you think the recent
changes in how EXPLAIN returns its results are a protocol change, then
yeah it's a protocol change ...

			regards, tom lane

Wed, 20 Nov 2002 00:24:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL) 
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Neil Conway <neilc@samurai.com> writes:
> > Why would this be needed? Couldn't we rely on the client programmer to
> > know that '$n is of type foo', and then pass the appropriately-typed
> > data to EXECUTE?
> 
> I don't think so.  You may as well ask why we waste bandwidth on passing
> back info about the column names and types of a SELECT result ---
> shouldn't the client know that already?  There are lots of middleware
> layers that don't know it, or at least don't want to expend a lot of
> code on trying to deduce it.

Fair enough -- although there's a major difference between the
meta-data stored about tables (which are permanent database objects
and are typically complex), and prepared statements (which (at
present) are only stored for the duration of the current connection,
and are relatively simple: many statements will not have more than a
couple params). Arguably, the difference is enough to make it
nonessential that we provide client programmers with that information.

> > If we *do* need an API for this, ISTM that by adding protocol-level
> > support for PREPARE/EXECUTE, this shouldn't be too difficult to do
> > (and analogous to the way we pass back type information for SELECT
> > results).
> 
> I'm not sure what you mean by protocol-level support

I was thinking something along the lines of making prepared statements
actually part of the protocol itself -- i.e. have a client message for
'PrepareStatement', a server message for 'StatementDescriptor', a
client message for 'ExecuteStatement', a server message for
'ExecuteResults', and so on. The message that returns the statement
descriptor would provide the necessary typo info, which the client's
language interface can make available to them in a convenient
fashion. As I mentioned, this would also allow EXECUTE parameters to
bypass the parser, which a couple people have remarked is slow when
inputting megabytes of data in a query string.

> the one idea I had was to return a table (equivalent to a SELECT
> result) listing the parameter types.  This would not break libpq,
> for example, so arguably it's not a protocol change.

Hmmm... that would work, although it strikes me as being a bit messy
to use tables to return data intended solely for machine use. As far
as changing the protocol, I think there's justification for doing that
in 7.4 anyway -- so ISTM that either solution would work pretty
well.

If anyone would prefer one or the other of these APIs, please speak
up...

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


20 Nov 2002 01:12:40 -0500
Neil Conway <neilc@samurai.com>
Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
Neil Conway <neilc@samurai.com> writes:
> Fair enough -- although there's a major difference between the
> meta-data stored about tables (which are permanent database objects
> and are typically complex), and prepared statements (which (at
> present) are only stored for the duration of the current connection,
> and are relatively simple: many statements will not have more than a
> couple params). Arguably, the difference is enough to make it
> nonessential that we provide client programmers with that information.

I forgot to point out this: if the client programmer could conveniently
provide that info, we'd not be having this discussion, because he could
just as well include the datatypes in the PREPARE command to meet our
existing syntax.  The fact that we are getting complaints about the
syntax is sufficient evidence that it's not always reasonable to expect
client-side code to know the datatypes.  (I think this comes mainly from
the fact that client-side code is not monolithic but tends to consist
of multiple layers.  Some of those layers may be expected to know
a-priori what datatypes a query involves, but others will not know.)

			regards, tom lane

Wed, 20 Nov 2002 01:27:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL) 
On Tue, 19 Nov 2002 15:13:49 -0800, David Wheeler wrote:
> On Tuesday, November 19, 2002, at 04:19 AM, Peter Haworth wrote:
>
> > You'd also need a runtime check that the server you connected to was of
> > a sufficiently high version. I realise it would make things more
> > complicated, but would it be possible to keep the manual transaction
> > starting behaviour, as well as adding the 7.3 behaviour, and deciding
> > which to do based on the server version?
>
> I think I'd rather do it at compile-time, depending on the PosgreSQL
> libraries available. Folks who compile against 7.3 and then connect to
> 7.2 get what they ask for, IMO.

Fair enough, but at least check the server version on connection, and bail
if it's not high enough.

-- 
	Peter Haworth	pmh@edison.ioppublishing.com
"I couldn't even find anything to read. The hotel shop 
 only had two decent books, and I'd written both of them."
		-- Douglas Adams, the Salmon of Doubt

Wed, 20 Nov 2002 15:56:46 +0000
"Peter Haworth" <pmh@edison.ioppublishing.com>
Re: DBD::PostgreSQL
On Wednesday, November 20, 2002, at 07:56  AM, Peter Haworth wrote:

> Fair enough, but at least check the server version on connection, and 
> bail
> if it's not high enough.

What's the easiest way to get the version on connection?

Thanks,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Wed, 20 Nov 2002 19:02:51 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Thu, 2002-11-21 at 03:02, David Wheeler wrote:
> On Wednesday, November 20, 2002, at 07:56  AM, Peter Haworth wrote:
> 
> > Fair enough, but at least check the server version on connection, and 
> > bail
> > if it's not high enough.
> 
> What's the easiest way to get the version on connection?

SELECT version();

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "A new commandment I give unto you; That ye love one 
      another. As I have loved you, so ye also must love one
      another.  By this shall all men know that ye are my 
      disciples, if ye have love one to another."    
                         John 13:34,35 


21 Nov 2002 07:50:03 +0000
Oliver Elphick <olly@lfix.co.uk>
Re: DBD::PostgreSQL
On Tue, Nov 19, 2002 at 03:13:49PM -0800, David Wheeler wrote:
> On Tuesday, November 19, 2002, at 04:19  AM, Peter Haworth wrote:
> 
> >as well as adding the 7.3 behavior, and deciding which to do based on 
> >the
> >server version?
> 
> I think I'd rather do it at compile-time, depending on the PosgreSQL 
> libraries available. Folks who compile against 7.3 and then connect to 
> 7.2 get what they ask for, IMO.

Ack! I hope this isn't true. Think about it: My development machine has
the latest and greatest PostgreSQL installed (along with
Perl/DBI/dbish), I'm testing the difference between PostgreSQL 7.2 and
7.3 in my application. Time to connect with the production server to
research a difference (the 7.2 base). 

Do I need to maintain two copies of DBD::Postgres (or DBD::Pg ... not
sure of the new name) one compiled for 7.2 and one for 7.3?

I understand the pain of supporting different functions for different
versions.

Tom

-- 
Thomas A. Lowery
See DBI/FAQ http://xmlproj.dyndns.org/cgi-bin/fom

Thu, 21 Nov 2002 08:15:40 -0500
"Thomas A. Lowery" <tl-lists@stlowery.net>
Re: DBD::PostgreSQL compile time /run time version
On Wednesday, November 20, 2002, at 11:50  PM, Oliver Elphick wrote:

> SELECT version();

Right, I've used that before. Too bad it doesn't just return the 
version number. But I can parse it out.

Thanks,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Thu, 21 Nov 2002 08:13:52 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL
On Thursday, November 21, 2002, at 05:15  AM, Thomas A. Lowery wrote:

> Do I need to maintain two copies of DBD::Postgres (or DBD::Pg ... not
> sure of the new name) one compiled for 7.2 and one for 7.3?
>
> I understand the pain of supporting different functions for different
> versions.

This would be the best reason to change the name of the driver. But I'm 
still on the fence wrt all this, and it's likely to be months before I 
have something workable and it's time to decide how to handle the issue 
of compatibility.

Regards,

David

-- 
David Wheeler                                     AIM: dwTheory
david@wheeler.net                                 ICQ: 15726394
http://david.wheeler.net/                      Yahoo!: dew7e
                                                Jabber: Theory@jabber.org


Thu, 21 Nov 2002 15:38:43 -0800
David Wheeler <david@wheeler.net>
Re: DBD::PostgreSQL compile time /run time version