Horton

Thread

  1. 7.3 Prepared statements

    Ericson Smith <eric@did-it.com> — 2002-12-17T15:19:43Z

    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

  2. Re: 7.3 Prepared statements

    Neil Conway <neilc@samurai.com> — 2002-12-17T16:31:52Z

    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

  3. Re: 7.3 Prepared statements

    Francisco J Reyes <fran@natserv.net> — 2002-12-26T17:34:04Z

    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?

  4. Re: 7.3 Prepared statements

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-01-03T03:09:14Z

    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

  5. Re: [GENERAL] 7.3 Prepared statements

    Charles H. Woloszynski <chw@clearmetrix.com> — 2003-01-03T13:19:38Z

    I saw this on the GENERAL list and this 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

  6. Re: [GENERAL] 7.3 Prepared statements [Viruschecked]

    Patric Bechtel <bechtel@ipcon.de> — 2003-01-03T14:21:36Z

    On Fri, 03 Jan 2003 08:19:38 -0500, Charles H. Woloszynski wrote:

Please see my answer on the list... it was especially addressed to you!

Patric


>I saw this on the GENERAL list and this 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
>
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

  7. Re: 7.3 Prepared statements

    Alan Gutierrez <ajglist@izzy.net> — 2003-01-06T10:35:50Z

    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

  8. Re: 7.3 Prepared statements

    Neil Conway <neilc@samurai.com> — 2003-01-07T06:50:44Z

    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

  9. Re: 7.3 Prepared statements

    Alan Gutierrez <ajglist@izzy.net> — 2003-01-07T13:00:25Z

    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

  10. Re: 7.3 Prepared statements

    Neil Conway <neilc@samurai.com> — 2003-01-09T03:02:15Z

    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