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