X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id CC4D59FA5FB for ; Fri, 16 Jun 2006 11:29:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 44215-01 for ; Fri, 16 Jun 2006 11:29:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.202]) by postgresql.org (Postfix) with ESMTP id 7BF099FA42B for ; Fri, 16 Jun 2006 11:29:15 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id h30so453219wxd for ; Fri, 16 Jun 2006 07:29:14 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=gZig0SChQIdRPtGMRxbDHfycH5vwo6gtEzdfTKkFZOsPJ7f2dteFfubfu5YjBnQzXS4sT7ncqKD7mozy8JztXI4cdyefsrdiW/laB+IroiJwT1Rc8ENEWHYrLF9/umf31d0Y+df/whZ3/7IFlQhKIxz/7C0Ak5yeETzeThOfp7I= Received: by 10.70.37.13 with SMTP id k13mr4056227wxk; Fri, 16 Jun 2006 07:29:14 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Fri, 16 Jun 2006 07:29:13 -0700 (PDT) Message-ID: Date: Fri, 16 Jun 2006 09:29:13 -0500 From: "Aaron Bono" To: "Michael Fuhr" Subject: Re: sessions and prepared statements Cc: "Rod Taylor" , "John DeSoi" , "chester c young" , pgsql-sql@postgresql.org In-Reply-To: <20060616130921.GA31604@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_61598_33211359.1150468153980" References: <20060615154930.44620.qmail@web54312.mail.yahoo.com> <4B156402-1B7D-46E2-8A4F-9DBD3F119CB7@pgedit.com> <1150462516.777.550.camel@home> <20060616130921.GA31604@winnie.fuhr.org> X-Google-Sender-Auth: 73567fa3d6bb4732 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/210 X-Sequence-Number: 25446 ------=_Part_61598_33211359.1150468153980 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline If you are using pooled connections, doesn't PostgreSQL manage the prepared statements for you? I would expect that, once I prepare a statement, if I attempt to do it again, PostgreSQL would say, "righty then, already done it, here you go". Then again, I don't know what PostgreSQL does under the covers. This seems like a lot of work to squeek out a small amount of efficiency. Would it really give you much value? -Aaron Bono On 6/16/06, Michael Fuhr wrote: > > On Fri, Jun 16, 2006 at 08:55:16AM -0400, Rod Taylor wrote: > > BEGIN; > > SAVEPOINT; > > SELECT * FROM temporary_prepared_statement; > > ROLLBACK TO SAVEPOINT < on failure>; > > CREATE TEMPORARY TABLE temporary_prepared_statement ...; > > COMMIT; > > > > Now you have a place to store and retrieve prepared connection state for > > the lifetime of the database backend provided PHP doesn't remove > > temporary tables on the connection. > > This doesn't help today, but 8.2 will have a pg_prepared_statements > view. > > http://archives.postgresql.org/pgsql-committers/2006-01/msg00143.php > > http://developer.postgresql.org/docs/postgres/view-pg-prepared-statements.html > > test=> PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1; > test=> \x > Expanded display is on. > test=> SELECT * FROM pg_prepared_statements; > -[ RECORD 1 > ]---+---------------------------------------------------------- > name | stmt > statement | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = > $1; > prepare_time | 2006-06-16 07:07:41.682999-06 > parameter_types | {integer} > from_sql | t > > -- > Michael Fuhr ------=_Part_61598_33211359.1150468153980 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline If you are using pooled connections, doesn't PostgreSQL manage the prepared statements for you?  I would expect that, once I prepare a statement, if I attempt to do it again, PostgreSQL would say, "righty then, already done it, here you go".  Then again, I don't know what PostgreSQL does under the covers.

This seems like a lot of work to squeek out a small amount of efficiency.  Would it really give you much value?

-Aaron Bono

On 6/16/06, Michael Fuhr <mike@fuhr.org> wrote:
On Fri, Jun 16, 2006 at 08:55:16AM -0400, Rod Taylor wrote:
> BEGIN;
> SAVEPOINT;
> SELECT * FROM temporary_prepared_statement;
> ROLLBACK TO SAVEPOINT < on failure>;
> CREATE TEMPORARY TABLE temporary_prepared_statement ...;
> COMMIT;
>
> Now you have a place to store and retrieve prepared connection state for
> the lifetime of the database backend provided PHP doesn't remove
> temporary tables on the connection.

This doesn't help today, but 8.2 will have a pg_prepared_statements
view.

http://archives.postgresql.org/pgsql-committers/2006-01/msg00143.php
http://developer.postgresql.org/docs/postgres/view-pg-prepared-statements.html

test=> PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
test=> \x
Expanded display is on.
test=> SELECT * FROM pg_prepared_statements;
-[ RECORD 1 ]---+----------------------------------------------------------
name            | stmt
statement       | PREPARE stmt (integer) AS SELECT * FROM foo WHERE x = $1;
prepare_time    | 2006-06-16 07:07: 41.682999-06
parameter_types | {integer}
from_sql        | t

--
Michael Fuhr
------=_Part_61598_33211359.1150468153980--