Re: [INTERFACES] DBD::PostgreSQL compile time /run time version
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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] 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[EMAIL PROTECTED] 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
Re: [INTERFACES] DBD::PostgreSQL
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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL compile time /run time version
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
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
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 [EMAIL PROTECTED] "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
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] 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 [EMAIL PROTECTED] "To vacillate or not to vacillate, that is the question ... or is it?"
Re: [INTERFACES] DBD::PostgreSQL
Jon Jensen <[EMAIL PROTECTED]> writes: > 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 can see the possiblity of significant changes in behavior (in the direction of correctness) that might make it quite smart to change the driver name to emphasize that this really requires that you look at your code again---and allowing those who don't have that luxury to continue to use the old module for old code, while using the new module for new code. So, yeah, I think it would be rather smart if the changes end up being as sweeping as it seems they might. Mike.
Re: [INTERFACES] 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 [EMAIL PROTECTED]
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
David Wheeler <[EMAIL PROTECTED]> 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
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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
David Wheeler <[EMAIL PROTECTED]> 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
Re: [INTERFACES] DBD::PostgreSQL
On Monday, November 18, 2002, at 07:23 AM, Michael Alan Dorman wrote: Perhaps I misunderstand how things are happening in the low-level protocol, but if a connection to postgres is in autocommit mode by default, which I understand to be the case, then all you need to do is have the begin_work and commit and rollback commands complain if AutoCommit is 1, and everything else complain if AutoCommit is 0 and you're not in a transaction. Yes, but one needs to be able to tell if a transaction is currently in progress or not. Right now, DBD::Pg assumes that if AutoCommit is 0, then a transaction is in progress. I don't think that's the best approach. You've already got the AutoCommit boolean, so you would only need one more, say, InTransaction, to recognize the AutoCommit 0/no begin_work situation. Right. I think I can use the init_commit flag in DBD::Pg for that purpose, since I can't really tell that it's used for much else at the moment. If I have misunderstood and the situation is not this obvious, then I at least agree with Tom here---issuing begin before the first command in a new transaction can bite you, and I have, in fact, been bitten by exactly this when using DBD::Pg with Apache::DBI. Right, all the more reason to change the behavior. Thanks Michael. Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
On Mon, Nov 18, 2002 at 11:19:25AM -0500, Tom Lane wrote:
> David Wheeler <[EMAIL PROTECTED]> 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.
Re: [INTERFACES] 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
[EMAIL PROTECTED] ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
Tom Lane <[EMAIL PROTECTED]> writes: > David Wheeler <[EMAIL PROTECTED]> 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. Perhaps I misunderstand how things are happening in the low-level protocol, but if a connection to postgres is in autocommit mode by default, which I understand to be the case, then all you need to do is have the begin_work and commit and rollback commands complain if AutoCommit is 1, and everything else complain if AutoCommit is 0 and you're not in a transaction. You've already got the AutoCommit boolean, so you would only need one more, say, InTransaction, to recognize the AutoCommit 0/no begin_work situation. If I have misunderstood and the situation is not this obvious, then I at least agree with Tom here---issuing begin before the first command in a new transaction can bite you, and I have, in fact, been bitten by exactly this when using DBD::Pg with Apache::DBI. Mike.
Re: [INTERFACES] DBD::PostgreSQL
David Wheeler <[EMAIL PROTECTED]> 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
Re: [INTERFACES] 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
Re: [INTERFACES] 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 [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
On Monday 18 November 2002 04:00, David Wheeler wrote: (B> Dear Fellow DBI and PostgreSQL Hackers, (B (B(...) (B> Okay, sorry for all the questions. My motivation is to make a new (B> PostgreSQL DBI driver that's one of the best DBI drivers around. Any (B> help would go a long way toward helping me to reach my goal. (B (BCount me in. I'm still in a slight state of shock after wondering over (Bto CPAN to find out how DBD::Pg was coming along ;-). At the (Bvery least I can do testing and documentation, and quite possibly (B"grunt work". Anything else will depend on how quickly I can (Bacquaint myself with the internals of DBI. (Note to self: do not believe (Bthis is impossible or anything). Perl is my main (Bdevelopment language, and I used to work a lot with C. (B (BFor clarification: is DBD::Postgres intended to replace DBD::Pg, and are (Bany maintenance releases of the latter planned (e.g. in conjunction with (Bthe PostgreSQL 7.3. release)? (B (BIan Barwick ([EMAIL PROTECTED]
Re: [INTERFACES] DBD::PostgreSQL
David Wheeler <[EMAIL PROTECTED]> 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
Re: [INTERFACES] 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
