7.3 Prepared statements

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 would ask on the jdbc lists. I am Cc'ing them, and removing the
general list.

---------------------------------------------------------------------------

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.

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

-- 
  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

I've been wondering about that for Perl DBI/DBD too.

Link.

At 11:18 PM 1/2/03 -0500, Charles H. Woloszynski wrote:

On Thu, 2 Jan 2003 23:47:05 -0500 (EST), Bruce Momjian wrote:

I would ask on the jdbc lists. I am Cc'ing them, and removing the
general list.

---------------------------------------------------------------------------

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.

Thanks,

Charlie

As I dipped my nose a bit deeper into that the last few days, I think
I can tell you that the JDBC driver (7.3.1 and the current CVS)
doesn't use server side prepared statements automatically.
I changed that with a patch I announced yesterday. As soon as it will
be accepted, it will.
If you want to try it out *NOW*, you should to something like this:
((org.postgresql.jdbc1.AbstractJdbc1Statement)statement).setUseServerPrepare(true);
It's kind of clumsy, and does clue your app to pgsql a bit, but for
a test, it's ok.
(You could also try out my patch, it's on the pgsql-patches list :-))
Don't overestimate the performance win for prepared statements, as the
optimizer can NOT really calculated the perfect plan, if the statement
is too unspecific.
At the moment there's a bug regarding boolean values in server side
prepared statements. My patch fixes this, too. I hope it will be
applied soon...

tia

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

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

Charles,

You can have the jdbc driver use stored plans, but that is not the
default behavior. There is a method on the org.postgresql.PGStatement
object to enable using a stored plans.

It is not the default behavior in 7.3 for the following reasons:

1) The functionality on the server is new and therefore there are
probably a few bugs yet to be found. I didn't want to destabilize the
overall jdbc driver by relying on new functionality.

2) The implementation on the server will not allow all current uses of
JDBC PreparedStatements to work (i.e. prepared statements that do
multiple sql statements in one call, ususally done to reduce number of
network roundtrips when needing to issue many sql calls at the same time).

3) The current implementation on the server will cause a decrease in
performance if you only use the PreparedStatement once. Since the
implementation of server side prepared statements requires multiple
roundtrips to prepare, execute and close, there will be more network
traffic and thus decreased performance if the JDBC prepared statement is
only used once. And my experience is that the vast majority of JDBC
prepared statements are only used once.

4) This is version one of the functionality. We can learn from it to
understand how it can be improved for 7.4.

thanks,
--Barry

Bruce Momjian wrote:

Barry:

Thanks for the feedback. This helps alot. Feedback/questions below.

Barry Lind wrote:

Charles,

You can have the jdbc driver use stored plans, but that is not the
default behavior. There is a method on the org.postgresql.PGStatement
object to enable using a stored plans.

It is not the default behavior in 7.3 for the following reasons:

1) The functionality on the server is new and therefore there are
probably a few bugs yet to be found. I didn't want to destabilize the
overall jdbc driver by relying on new functionality.

2) The implementation on the server will not allow all current uses of
JDBC PreparedStatements to work (i.e. prepared statements that do
multiple sql statements in one call, ususally done to reduce number of
network roundtrips when needing to issue many sql calls at the same
time).

Do you mean that the driver, in this mode, will disallow various forms
of JDBC prepared statements, or that the stored plan support will not be
supported if the query is of one of these forms? I hope it is the
latter, as that would make porting to the new driver easier and avoid
the re-port to a new driver issue. We use PreparedStatements *for
everything* and it is part of the framework that we use to do it this
way. I think it would make the new driver unusable to us if we could
not use the PreparedStatements for certain, currently legal, SQL.

3) The current implementation on the server will cause a decrease in
performance if you only use the PreparedStatement once. Since the
implementation of server side prepared statements requires multiple
roundtrips to prepare, execute and close, there will be more network
traffic and thus decreased performance if the JDBC prepared statement
is only used once. And my experience is that the vast majority of
JDBC prepared statements are only used once.

Is that saying that the performance is reduced with the *new
functionality* more than current PreparedStatement processing, or that
PreparedStatements are in genral, slower? Sounds like the former, and
your observation that prepared statements are mostly used only once is
kinda troublesome. Sounds like this feature will need to be
enabled/disabled in the URL (I think I saw a posting about that from
you), to avoid impacting applications that do not re-use prepared
statements. Our framework allows for re-use, so I am looking forward to
improved performance once we start in earnest the move to 7.3.x.

4) This is version one of the functionality. We can learn from it to
understand how it can be improved for 7.4.

I hope we can help get some feedback to you on this. Do you have a
timeline for 7.4 features?

Thanks,

Charlie

Charles H. Woloszynski wrote:

2) The implementation on the server will not allow all current uses of
JDBC PreparedStatements to work (i.e. prepared statements that do
multiple sql statements in one call, ususally done to reduce number of
network roundtrips when needing to issue many sql calls at the same
time).

Do you mean that the driver, in this mode, will disallow various forms
of JDBC prepared statements, or that the stored plan support will not be
supported if the query is of one of these forms? I hope it is the
latter, as that would make porting to the new driver easier and avoid
the re-port to a new driver issue. We use PreparedStatements *for
everything* and it is part of the framework that we use to do it this
way. I think it would make the new driver unusable to us if we could
not use the PreparedStatements for certain, currently legal, SQL.

The implementation in the current driver is such that you need to enable
the use of server side prepare on a statement by statement basis
(default is not to use it). So you should only enable if for those
statements for which it will work. To illustrate the problem here is an
example:

pstat = conn.prepareStatement("insert into foo values (?); update bar
set y = ?;);

When run in server prepared mode this will issue the following
statements to the server:

prepare abc(int, int) as insert into foo values ($1); update bar set y =
($2);

which will fail since the prepare statement is ended by the first
semicolon. So this would be an example of a JDBC prepared statement
that works fine currently but would fail when used with server side
prepared statements.

3) The current implementation on the server will cause a decrease in
performance if you only use the PreparedStatement once. Since the
implementation of server side prepared statements requires multiple
roundtrips to prepare, execute and close, there will be more network
traffic and thus decreased performance if the JDBC prepared statement
is only used once. And my experience is that the vast majority of
JDBC prepared statements are only used once.

Is that saying that the performance is reduced with the *new
functionality* more than current PreparedStatement processing, or that
PreparedStatements are in genral, slower? Sounds like the former, and
your observation that prepared statements are mostly used only once is
kinda troublesome. Sounds like this feature will need to be
enabled/disabled in the URL (I think I saw a posting about that from
you), to avoid impacting applications that do not re-use prepared
statements. Our framework allows for re-use, so I am looking forward to
improved performance once we start in earnest the move to 7.3.x.

Yes the former. That is why the use of server side prepared statements
is not the default. This can hopefully be addressed by changes in the
FE/BE protocol in 7.4 so that there isn't any difference in performance.

4) This is version one of the functionality. We can learn from it to
understand how it can be improved for 7.4.

I hope we can help get some feedback to you on this. Do you have a
timeline for 7.4 features?

Traditionally the postgres release cycle is 6-12 months and we are about
2 months into it.

Thanks,

Charlie

thanks,
--Barry

Barry Lind wrote:

The implementation in the current driver is such that you need to
enable the use of server side prepare on a statement by statement
basis (default is not to use it). So you should only enable if for
those statements for which it will work. To illustrate the problem
here is an example:

pstat = conn.prepareStatement("insert into foo values (?); update bar
set y = ?;);

When run in server prepared mode this will issue the following
statements to the server:

prepare abc(int, int) as insert into foo values ($1); update bar set y
= ($2);

which will fail since the prepare statement is ended by the first
semicolon. So this would be an example of a JDBC prepared statement
that works fine currently but would fail when used with server side
prepared statements.

Is the driver's mode set using a URL argument or an API call? I thought
I saw mention of a URL argument. Is the API call something that
persists between connection uses? We use a connection pool and I want
to get the right semantics for the use of this feature.

Personally, I'd prefer that the server scan for a non-quoted semicolon
and revert to non-prepared' operation. The cost of the extra scan is
likely to be minimal and would allow those folks who use a connection
pool to use this feature and have it work when it can and not interfere
when it might. I am guessing that you may have already parsed the
statement looking for ?'s

If so, wouldn't the supression of the 'prepare' be relatively
straight-forward?

Charlie

--

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 wrote:

Barry Lind wrote:

The implementation in the current driver is such that you need to
enable the use of server side prepare on a statement by statement
basis (default is not to use it). So you should only enable if for
those statements for which it will work. To illustrate the problem
here is an example:

pstat = conn.prepareStatement("insert into foo values (?); update bar
set y = ?;);

When run in server prepared mode this will issue the following
statements to the server:

prepare abc(int, int) as insert into foo values ($1); update bar set y
= ($2);

which will fail since the prepare statement is ended by the first
semicolon. So this would be an example of a JDBC prepared statement
that works fine currently but would fail when used with server side
prepared statements.

Is the driver's mode set using a URL argument or an API call? I thought
I saw mention of a URL argument. Is the API call something that
persists between connection uses? We use a connection pool and I want
to get the right semantics for the use of this feature.
Personally, I'd prefer that the server scan for a non-quoted semicolon
and revert to non-prepared' operation. The cost of the extra scan is
likely to be minimal and would allow those folks who use a connection
pool to use this feature and have it work when it can and not interfere
when it might. I am guessing that you may have already parsed the
statement looking for ?'s
If so, wouldn't the supression of the 'prepare' be relatively
straight-forward?

There is no url argument currently. There is a proposed patch that adds
that functionality but it won't be there until 7.4. As far as parsing
the query looking for a semi colon that is probably what will be done
for 7.4, but again that isn't there for 7.3.

The API call sets a member variable on the PreparedStatement object so
once set it will remain set for that object until explicitly unset or
the PreparedStatement is closed.

--Barry

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