Are stored procedures always kept in memory?

Started by Roman Golisover 13 years ago2 messagesgeneral
Jump to latest
#1Roman Golis
rgolis@aps-holding.com

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

#2Pavel Stehule
pavel.stehule@gmail.com
In reply to: Roman Golis (#1)
Re: Are stored procedures always kept in memory?

Hello

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?

procedures living in pg_proc table. Before call procedure is loaded,
compiled (to abstract syntax tree) and compiled code (tree) is stored
to session cache. Cache is released after logout or function's update.

Regards

Pavel Stehule

Show quoted text

Thanks for reply.

R.G.

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