Re: 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