Skip site navigation (1) Skip section navigation (2)

Re: Prepared statements considered harmful

From: mark(at)mark(dot)mielke(dot)cc
To: AgentM <agentm(at)themactionfaction(dot)com>
Cc: postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Prepared statements considered harmful
Date: 2006-08-31 15:53:24
Message-ID: 20060831155324.GA10949@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, Aug 31, 2006 at 11:36:36AM -0400, AgentM wrote:
> On Aug 31, 2006, at 11:18 , mark(at)mark(dot)mielke(dot)cc wrote:
> >I'm attempting to understand why prepared statements would be used for
> >long enough for tables to change to a point that a given plan will
> >change from 'optimal' to 'disastrous'.
> >
> >Wouldn't this require that the tables are completely re-written, or
> >that their data is drastically updated? For my own tables, most of the
> >data remains static for months on end. Data is accumulated. Small
> >changes are made. I don't see why a prepared statement used over a
> >24 hour period would ever become disastrous.

> Scenario: A web application maintains a pool of connections to the  
> database. If the connections have to be regularly restarted due to a  
> postgres implementation detail (stale plans), then that is a database  
> deficiency.

Or a JDBC deficiency. Nobody is forcing JDBC to automatically reuse a
prepared plan indefinately. If automatically prepared, it can
regenerate them whenever it wishes.

Does Oracle automatically regenerate prepared plans on occasion?

I don't consider it a deficiency. It is doing exactly what you are
asking it to do. That it isn't second guessing you isn't a deficiency.
For all PostgreSQL knows, your tables are not changing such that a
query a week later is suddenly disastrous because the consistency of
your data has changed drastically, and what you prepared a week ago,
and chose to execute today, is still the optimal plan.

Cheers,
mark

-- 
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-08-31 16:04:51
Subject: Re: Prepared statements considered harmful
Previous:From: Andrew - SupernewsDate: 2006-08-31 15:47:20
Subject: Re: Prepared statements considered harmful

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group