Re: DBD::PostgreSQL

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