RQ: Prepared statements used by multiple connections

I need a mechanism to keep my queries in optimized
state so that multiple processes can use them.

You should use stored procedures then.

For instance, say you want to keep 'SELECT * FROM table WHERE id=x'
prepared. You would go:

CREATE OR REPLACE FUNCTION get_table_id(integer) RETURNS SETOF table AS
'SELECT * FROM table WHERE id=$1' LANGUAGE SQL;

PostgreSQL will store a prepared version of that statement after its
first use.

You use it like this:

SELECT * FROM get_table_id(3);

Chris

Christopher Kings-Lynne wrote:

I need a mechanism to keep my queries in optimized state so that
multiple processes can use them.

You should use stored procedures then.

For instance, say you want to keep 'SELECT * FROM table WHERE id=x'
prepared. You would go:

CREATE OR REPLACE FUNCTION get_table_id(integer) RETURNS SETOF table AS
'SELECT * FROM table WHERE id=$1' LANGUAGE SQL;

PostgreSQL will store a prepared version of that statement after its
first use.

... a prepared version that is local to the backend that invokes the
function, yes (i.e. it will be planned once per backend). So ISTM this
is equivalent functionality to what you can get using PREPARE or the
extended query protocol.

There currently isn't support for prepared queries that span multiple
connections. There has been prior discussion of the topic, so try
searching the pgsql-hackers archive. There are some implementation
issues that require thought (e.g. managing dependencies, storing a
potentially unbounded set of prepared queries in a finite amount of shmem).

-Neil

... a prepared version that is local to the backend that invokes the
function, yes (i.e. it will be planned once per backend). So ISTM this
is equivalent functionality to what you can get using PREPARE or the
extended query protocol.

Are you sure it's only per-backend? I thought I tested it and it seemed
to prepare it everywhere... oh well.

Either way, it avoids the problem with prepared queries in that you
cannot know in advance if your query has already been prepared or not.

Chris

On Tue, Jan 25, 2005 at 10:36:34AM +0000, Christopher Kings-Lynne wrote:

... a prepared version that is local to the backend that invokes the
function, yes (i.e. it will be planned once per backend). So ISTM this
is equivalent functionality to what you can get using PREPARE or the
extended query protocol.

Are you sure it's only per-backend? I thought I tested it and it seemed
to prepare it everywhere... oh well.

How did you test it? You can use settings like debug_print_plan
and client_min_messages to see what's happening in each backend.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

It looks it couldn't happen this a way.
Did somebody find out an alternative.
Is reasonable some idea based on a connection pool ?
-Bozhidar

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

... a prepared version that is local to the backend that invokes the
function, yes (i.e. it will be planned once per backend). So ISTM

this

is equivalent functionality to what you can get using PREPARE or the
extended query protocol.

Are you sure it's only per-backend? I thought I tested it and it

seemed

to prepare it everywhere... oh well.

Plpgsql functions at the least are compiled by each backend. I take
advantage of this...I use schemas and I don't have to keep a copy of the
function for each dataset. I think vanilla sql functions might be
different.

Either way, it avoids the problem with prepared queries in that you
cannot know in advance if your query has already been prepared or not.

Yep. I like things the way they are, but I can feel the pain of
applications that don't (or can't) keep connections open.

Merlin