DBD::PostgreSQL
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
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
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
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
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
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.
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.
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
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
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
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
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.
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
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
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
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
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
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
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
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
