Thread: 7.3 Prepared statements

7.3 Prepared statements

From
Ericson Smith
Date:
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




Re: 7.3 Prepared statements

From
Neil Conway
Date:
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




Re: 7.3 Prepared statements

From
Francisco J Reyes
Date:
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?


Re: 7.3 Prepared statements

From
Bruce Momjian
Date:
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

Re: 7.3 Prepared statements

From
"Charles H. Woloszynski"
Date:
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






Re: 7.3 Prepared statements

From
Lincoln Yeoh
Date:
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.



Re: 7.3 Prepared statements

From
Alan Gutierrez
Date:
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


Re: 7.3 Prepared statements

From
Neil Conway
Date:
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




Re: 7.3 Prepared statements

From
Alan Gutierrez
Date:
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


Re: 7.3 Prepared statements

From
Neil Conway
Date:
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