Thread: Clearing plans

Is there a way to force a flush of all cached plans? Particularly, to force
re-evaluation of immutable stored procedures? Don't worry, it's a testing &
development thing, not something I want to do during production ;-)


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:
> Is there a way to force a flush of all cached plans?

Start a fresh connection.

            regards, tom lane

> Start a fresh connection.

OK. Better than having to restart the whole server, which is what I was
doing...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Are the plans cached per connection? Why not globally?

Thanks
Peter

On 1/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Ribe <scott_ribe@killerbytes.com> writes:
> > Is there a way to force a flush of all cached plans?
>
> Start a fresh connection.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On Thu, Jan 18, 2007 at 10:08:24PM +0100, Peter Kovacs wrote:
> Are the plans cached per connection? Why not globally?

Because global plan caching is much harder and nobody has done it yet?

If you use something like pgpool, you ofcourse get the advantages of
cached plans across multiple sessions, if that's what you're looking
for...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Thu, Jan 18, 2007 at 10:08:24PM +0100, Peter Kovacs wrote:
>> Are the plans cached per connection? Why not globally?

> Because global plan caching is much harder and nobody has done it yet?

The idea's been discussed before, and there are at least three problems
with it:

* unpredictability of shared-memory requirement

* plan cache would become a contention hot-spot

* much greater need for explicit cache management operations ("restart
your session" would no longer substitute for a flush-the-cache command)

            regards, tom lane

On Thu, 2007-01-18 at 12:21 -0700, Scott Ribe wrote:
> > Start a fresh connection.
>
> OK. Better than having to restart the whole server, which is what I was
> doing...
>

Just to clarify, you don't have to restart the whole server. All you
have to do is disconnect the client, and reconnect.

Regards,
    Jeff Davis

On 1/18/07, Scott Ribe <scott_ribe@killerbytes.com> wrote:
> Is there a way to force a flush of all cached plans? Particularly, to force
> re-evaluation of immutable stored procedures? Don't worry, it's a testing &
> development thing, not something I want to do during production ;-)

Also, somebody correct me if I'm off my rocker here, but immutable
procedures are re-evaluated for each execution...they are just folded
into a constant during plan phase.   To demonstrate this:

create temp sequence s;
postgres=# create function f() returns void as $$ select nextval('s');
$$ language sql;
create view v as select * from f();
create function g() returns bigint as $$ begin return f(); end; $$
language plpgqsl;
select * from v;
 f
---
 1
(1 row)

postgres=# select * from v;
 f
---
 2
(1 row)

postgres=# select g();
 g
---
 3
(1 row)

postgres=# select g();
 g
---
 4
(1 row)

merlin

> Also, somebody correct me if I'm off my rocker here, but immutable
> procedures are re-evaluated for each execution...they are just folded
> into a constant during plan phase.

You would need to actually create an immutable function in order to test how
immutable functions work ;-) And apparently you can only do that on
functions that return a value.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On 1/19/07, Scott Ribe <scott_ribe@killerbytes.com> wrote:
> > Also, somebody correct me if I'm off my rocker here, but immutable
> > procedures are re-evaluated for each execution...they are just folded
> > into a constant during plan phase.
>
> You would need to actually create an immutable function in order to test how
> immutable functions work ;-) And apparently you can only do that on
> functions that return a value.

d'oh! that was just a transcription error though...if you create f()
returning bigint and immutable it produces the same results.

merlin

> d'oh! that was just a transcription error though...if you create f()
> returning bigint and immutable it produces the same results.

So I see. But...

pedcard=# create function f2() returns boolean as $$ begin return 't'; end;
$$ language plpgsql immutable;
CREATE FUNCTION

pedcard=# create function f3() returns varchar as $$ begin if (select f2() =
't') then return 'yes'; else return 'no'; end if; end; $$ language plpgsql;
CREATE FUNCTION

pedcard=# select f3();
 f3
-----
 yes
(1 row)

pedcard=# create or replace function f2() returns boolean as $$ begin return
'f'; end; $$ language plpgsql immutable;
CREATE FUNCTION

pedcard=# select f3();
 f3
-----
 yes
(1 row)

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice