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.