Clearing plans

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

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