Thread: 7.3 Prepared statements
Hi all, With the new "PREPARE plan_name AS query" statement, 1. What is the lifetime of the plan created? 2. If it is for the session, does it last through persistent connections (eg. PHP) - Ericson Smith eric@did-it.com
On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: > 1. What is the lifetime of the plan created? From the PREPARE reference page: Prepared queries are stored locally (in the current backend), and only exist for the duration of the current database session. When the client exits, the prepared query is forgotten, and so it must be re-created before being used again. This also means that a single prepared query cannot be used by multiple simultaneous database clients; however, each client can create their own prepared query to use. > 2. If it is for the session, does it last through persistent connections > (eg. PHP) Yes -- but as noted above, relying on a prepared query to exist for a given connection is tricky. At best, you can write your client in such a way that whenever it EXECUTEs a query it didn't PREPARE itself, it is capable of issuing the requisite PREPARE statement if the EXECUTE fails. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On 17 Dec 2002, Neil Conway wrote: > On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: > > 1. What is the lifetime of the plan created? > > >From the PREPARE reference page: > > Prepared queries are stored locally (in the current backend), and only > exist for the duration of the current database session. What is the difference/advantage between a prepared query and a view?
A view is just syntaxic sugar added to a query that references the view. A preparted statement actually saves the execution plan for repeated execution. --------------------------------------------------------------------------- Francisco J Reyes wrote: > On 17 Dec 2002, Neil Conway wrote: > > > On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: > > > 1. What is the lifetime of the plan created? > > > > >From the PREPARE reference page: > > > > Prepared queries are stored locally (in the current backend), and only > > exist for the duration of the current database session. > > What is the difference/advantage between a prepared query and a view? > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce: Do you mean that, under JDBC with PG7.3, it will use the stored execution plan, or that we should ask the JDBC driver maintainers to work to use this feature to use the stored executiong plan? I am obviously interested in helping make this happen to improve the overall PostgreSQL solution. Should I contact someone specific about this to see if there are plans to make this happen in the near future? I think I have a solid framework to test its effectiveness with decent performance monitoring tools already in place. Thanks, Charlie Bruce Momjian wrote: >Sorry, I don't know if it does that yet, but I am sure it will if it >doesn't already. > >--------------------------------------------------------------------------- > >Charles H. Woloszynski wrote: > > >>I realize that this is a bit off topic, but your answer got me thinking. >> Do JDBC Prepared statements get the same saved execution plan support? >> We currently use PreparedStatements in our framework for JDBC access. >> We currently do not retain the PreparedStatement between uses, but if >>PostreSQL caches the execution plan, we may need to change our design. >> >>Thanks, >> >>Charlie >> >> >>Bruce Momjian wrote: >> >> >> >>>A view is just syntaxic sugar added to a query that references the view. >>>A preparted statement actually saves the execution plan for repeated >>>execution. >>> >>>--------------------------------------------------------------------------- >>> >>>Francisco J Reyes wrote: >>> >>> >>> >>> >>>>On 17 Dec 2002, Neil Conway wrote: >>>> >>>> >>>> >>>> >>>> >>>>>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: >>>>> >>>>> >>>>> >>>>> >>>>>>1. What is the lifetime of the plan created? >>>>>> >>>>>> >>>>>> >>>>>> >>>>>From the PREPARE reference page: >>>>> >>>>>Prepared queries are stored locally (in the current backend), and only >>>>>exist for the duration of the current database session. >>>>> >>>>> >>>>> >>>>> >>>>What is the difference/advantage between a prepared query and a view? >>>> >>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 4: Don't 'kill -9' the postmaster >>>> >>>> >>>> >>>> >>>> >>> >>> >>> >>> >>-- >> >> >>Charles H. Woloszynski >> >>ClearMetrix, Inc. >>115 Research Drive >>Bethlehem, PA 18015 >> >>tel: 610-419-2210 x400 >>fax: 240-371-3256 >>web: www.clearmetrix.com >> >> >> >> >> >> >> >> > > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com
I've been wondering about that for Perl DBI/DBD too. Link. At 11:18 PM 1/2/03 -0500, Charles H. Woloszynski wrote: >Bruce: > >Do you mean that, under JDBC with PG7.3, it will use the stored execution >plan, or that we should ask the JDBC driver maintainers to work to use >this feature to use the stored executiong plan? > >I am obviously interested in helping make this happen to improve the >overall PostgreSQL solution. Should I contact someone specific about this >to see if there are plans to make this happen in the near future? I think >I have a solid framework to test its effectiveness with decent performance >monitoring tools already in place.
Francisco J Reyes wrote: > On 17 Dec 2002, Neil Conway wrote: > > >>On Tue, 2002-12-17 at 10:19, Ericson Smith wrote: >> >>>1. What is the lifetime of the plan created? >> >>From the PREPARE reference page: >> >>Prepared queries are stored locally (in the current backend), and only >>exist for the duration of the current database session. > > > What is the difference/advantage between a prepared query and a view? > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster The idocs are still at 7.2.1. Is there an API for the prepared query interface in a later version of libpq? In libpq-fe.h there doesn't seem to be a statement structure or prepare call. Examples of prepared query use? Alan Gutierrez
On Mon, 2003-01-06 at 05:35, Alan Gutierrez wrote: > The idocs are still at 7.2.1. But the documentation itself has been updated, and is available online: http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/ No idea sure when the idocs will be updated... > Is there an API for the prepared query interface in a later version of > libpq? No, the prepared queries implemented in 7.3 are not protocol-level, they use a set of special SQL statements to prepare statements and then execute them. > Examples of prepared query use? http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-prepare.html http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-execute.html ... should cover enough stuff to get you started. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway wrote: > On Mon, 2003-01-06 at 05:35, Alan Gutierrez wrote: > >>Is there an API for the prepared query interface in a later version of >>libpq? > No, the prepared queries implemented in 7.3 are not protocol-level, they use > a set of special SQL statements to prepare statements and then execute them. >>Examples of prepared query use? > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-prepare.html > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-execute.html > > ... should cover enough stuff to get you started. Are any hackers working on a protocol level implementation? Has it been deemed unnecessary? This may be an area where I could help with some C development. Alan Gutierrez
On Tue, 2003-01-07 at 08:00, Alan Gutierrez wrote: > Are any hackers working on a protocol level implementation? Has it been deemed > unnecessary? There was some talk about doing a protocol-level implementation in 7.4. Since it would require a change in the FE/BE protocol (naturally), it would be nice to do it at the same time as a bunch of proposed minor cleanups to the FE/BE protocol, so we can do everything in one fell swoop. One benefit of a protocol-level implementation is that the data passed to the EXECUTE would be able to bypass the parser -- so if you're inserting gigabytes of text strings, it can avoid a potential bottleneck. > This may be an area where I could help with some C development. Please do. I'm a bit busy w/ school at the moment, but if you have any questions, I'd be happy to help. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC