Are stored procedures always kept in memory?

From: Roman Golis <rgolis(at)aps-holding(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Are stored procedures always kept in memory?
Date: 2012-08-07 14:12:11
Message-ID: a0cc3143-cad8-44fd-bfdc-5842a958c70d@aps-holding.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We run several instances of postgre in different countries, and we try keeping them as same as possible, in terms of structure of the tables and function definitions (except the content of schema "config", which differs between dbs). So if we need to implement some different algorithm per country, then we define a plpgsql function like this into each of our dbs:

BEGIN
select value from config.strings into country where name = 'country';
if country = 'CZ' then
-- Some computations here
elseif country = 'PL' then
-- Different calculations here
elseif country = 'RO' then
-- Yet another algorithm here
end if;
return (result);
END;

In this function, we get the value from a table config.strings (which contains a different value in each country's database), and based on this value we go through a specific if-branch. Simple. But reading this configuration value may involve reading from a disk.

So to avoid accessing the disk to fetch the country value, I would like to replace it by calling a function defined like this (in each db returning a different string indicating the country where db resides, of course):

create or replace function config.country () returns char(3) as $$ select 'CZ'::char(3) $$ language sql immutable;

And then call it like:

if config.country () = 'CZ' then
-- Some computations here

Now my questions is: Are the stored functions (both plpgsql and plain sql functions) kept always in a memory? Or they are stored similarly like tables, on the disk, reading them into memory when called and possibly release them from memory, if memory is needed for something else?

Thanks for reply.

R.G.

Disclaimer: http://www.aps-holding.com/disclaimer.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2012-08-07 14:19:04 Re: Are stored procedures always kept in memory?
Previous Message Magnus Hagander 2012-08-07 13:22:16 Pg_ctl promote -- wait for slave to be promoted fully ?