Thread: prepare()

Is there an advantage to using something like $dbh->prepare($sql) if
the SQL is going to be run once within the scope of the code?  The
code block may be run many times in a minute as in a function call ---

while (<>) {
    insert_something($_);
}

Will the prepare statement be cached @ the database even if it's
destroyed in the code/application?

> Is there an advantage to using something like $dbh->prepare($sql) if
> the SQL is going to be run once within the scope of the code?  The
> code block may be run many times in a minute as in a function call ---

> while (<>) {
>     insert_something($_);
> }

> Will the prepare statement be cached @ the database even if it's
> destroyed in the code/application?

The pg_prepared_statments view will give you information on prepared statments
currently resident in the database backend. I suggest you run your progam
(with a couple of "Enter to continue" breakpoints in the code) and, in another
session, select from pg_prepared_statements and see if the prepared statement
is still there.

I'll be trying this myself a bit later once I stabilise my system, so
watch this space!

Cheers,
Stuart.

> The pg_prepared_statments view will give you information on prepared statments
> currently resident in the database backend. I suggest you run your progam
> (with a couple of "Enter to continue" breakpoints in the code) and, in another
> session, select from pg_prepared_statements and see if the prepared statement
> is still there.

> I'll be trying this myself a bit later once I stabilise my system, so
> watch this space!

My system is stabilised and I'm looking at this now. I'm not getting a lot from
pg_prepared_statements yet: perhaps this view only reports on statements you've
prepared using PostgreSQL's PREPARE through their SQL interface, and not
DBI's DBD::Pg $dbh->prepare().

I know that a few versions back PostgreSQL's DBD driver didn't support prepared
statements (the operation was still there, it just wasn't doing the
whole magic),
I imagine that it does today but will investigate further.

Cheers,
Stuart.

> I know that a few versions back PostgreSQL's DBD driver didn't support prepared
> statements (the operation was still there, it just wasn't doing the
> whole magic),
> I imagine that it does today but will investigate further.

There's a very extensive writeup in the documentation of DBD::Pg,
perldoc DBD::Pg
and read the large section on the prepare() method.

Cheers,
Stuart.

"Stuart Cooper" <stuart.cooper@gmail.com> writes:
> My system is stabilised and I'm looking at this now. I'm not getting a
> lot from pg_prepared_statements yet: perhaps this view only reports on
> statements you've prepared using PostgreSQL's PREPARE through their
> SQL interface, and not DBI's DBD::Pg $dbh->prepare().

A quick look at the source code says that pg_prepared_statements should
show both statements prepared with the SQL-level PREPARE command, and
statements prepared through the wire-protocol Parse message (excluding
the "unnamed" statement in the latter case).  I'm not familiar with the
guts of DBD::Pg, however; it may not be "preparing" statements in any
sense that the backend knows about, but only massaging them locally to
the client library.  It likely matters which version of DBD::Pg you're
talking about, too, because the backend's support for this sort of thing
has been a moving target.

            regards, tom lane