Re: DBD::PostgreSQL

On Mon, 18 Nov 2002, David Wheeler wrote:

> 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 thought about that, but I was thinking that it would add quite a bit to
the complexity of the code (simpler is better all things being equal).

The problem with waiting until the fist stmt is that DBD::Pg also makes
requests to the DB behind the scenes, so now you have to keep track of tx
status before quering the db and turn it off and turn it back on when done
iff you are not already in tx....

If you are going to say that transactions are only BEGINed when the user
executes statement then it would be a bug for the driver to just decide to
start one on the user.

I don't know what would be worse: Always having a transaction open when I
am not in automcommit mode, and knowing about it, or having to worry
whether I am in transaction because of some stmt that I or some other
module issued (Including DBD::Pg).

> 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).

I look at it this way... make it simple and bullet-proof and then optimize
if needed.

> > 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.

Is that not what prepare_cached is for? One should only be preping
the statement once anyway, right?

And the statement gets parsed twice by DBD::Pg. One time in prepare and
One time in execute (for the substitution of parameters).

>
>
> 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".

Missed the q3.

PREPARE plan_name [ (datatype [, ...] ) ] AS query

I guess I read that as (datatype) being optional... I guess it is only
optional if there are no $1 &c. in the query, then.

Later,

-r