parsed queries (cursors) cashing issues

On 8/2/07, Sergey Moroz <smo@mgcp.com> wrote:

The problem is that I can't find the way to exclude query parsing (prepare
step) for custom queries. In other words I want to create a function that
accepts a query text with "$1, $2, etc." and variables as params, executes
the query and returns a set of record. I could use 'execute' in plpgsql but
in such case a query will be parsed each time it is called. I check SPI and
found the way to store execution plans for the duration of the session, but
no convenient way to check if the plan was already generated for the query.
So I should create and store hash table by myself, and associate plan
pointers and query hash by myself. I'm not a C/C++ guy so it's not an easy
task for me :). Is there any way to solve the problem? By the way - why not
to store hashes for queries and execution plans in a shared pool to have an
opportunity not to parse already parsed queries for any session as Oracle
does?

Looks like you want to cache the query plans and then simply execute
them in subsequent invocations. The answer to this is Prepared
statements. Go to
http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for
more details.

regards,
-- Sibte

No that is not I meant. The problem in Prepared statements is in that you
should determine SQL inside the function. I want to pass a query as a
parameter, as well as query parameters.
For example (I want to create a function like the following):

select *
from exec_query(
/*query text => */ 'select f1, f2 from table
where f3 = $1' ,
/*param1 => */ 1::integer
)
as (f1 integer, f2 text)

so function exec_query got a query text as parameter, query parameters,
executed it and returned result as SETOF. In case of such a query had been
executed at least once, prepare step should be excluded (stored execution
plan should be used).

On 8/3/07, Sibte Abbas <sibtay@gmail.com> wrote:

On 8/2/07, Sergey Moroz <smo@mgcp.com> wrote:

The problem is that I can't find the way to exclude query parsing

(prepare

step) for custom queries. In other words I want to create a function

that

accepts a query text with "$1, $2, etc." and variables as params,

executes

the query and returns a set of record. I could use 'execute' in plpgsql

but

in such case a query will be parsed each time it is called. I check SPI

and

found the way to store execution plans for the duration of the session,

but

no convenient way to check if the plan was already generated for the

query.

So I should create and store hash table by myself, and associate plan
pointers and query hash by myself. I'm not a C/C++ guy so it's not an

easy

task for me :). Is there any way to solve the problem? By the way - why

not

to store hashes for queries and execution plans in a shared pool to have

an

opportunity not to parse already parsed queries for any session as

Oracle

does?

Looks like you want to cache the query plans and then simply execute
them in subsequent invocations. The answer to this is Prepared
statements. Go to
http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html for
more details.

regards,
-- Sibte

--
Sincerely,
Sergey Moroz

On 8/3/07, Sergey Moroz <smo@mgcp.com> wrote:

No that is not I meant. The problem in Prepared statements is in that you
should determine SQL inside the function. I want to pass a query as a
parameter, as well as query parameters.
For example (I want to create a function like the following):

select *
from exec_query(
/*query text => */ 'select f1, f2 from table
where f3 = $1' ,
/*param1 => */ 1::integer
)
as (f1 integer, f2 text)

so function exec_query got a query text as parameter, query parameters,
executed it and returned result as SETOF. In case of such a query had been
executed at least once, prepare step should be excluded (stored execution
plan should be used).

In this case you need to store query text along with its plan name.
This will allow you to simply execute the plan each time a previously
parsed/planned query is executed.

However storing raw queries can be a *very* expensive operation, not
to mention the high cost of performing comparison on them. Due to the
associated cost, I'll
recommend using(and storing) hashes for query text.

If I were you, i'll write the hash calculation and storage and
retrieval functions in C and the top level function in Plpgsql.

Hope that helps.

regards,
-- Sibte

I think the same way :). But as I mentioned in the first letter I'm not a C
guy. So I wonder why doesn't postgres store hashes for all queries and
misses parsing step if not needed like Oracle does?

On 8/3/07, Sibte Abbas <sibtay@gmail.com> wrote:

On 8/3/07, Sergey Moroz <smo@mgcp.com> wrote:

No that is not I meant. The problem in Prepared statements is in that

you

should determine SQL inside the function. I want to pass a query as a
parameter, as well as query parameters.
For example (I want to create a function like the following):

select *
from exec_query(
/*query text => */ 'select f1, f2 from

table

where f3 = $1' ,
/*param1 => */ 1::integer
)
as (f1 integer, f2 text)

so function exec_query got a query text as parameter, query parameters,
executed it and returned result as SETOF. In case of such a query had

been

executed at least once, prepare step should be excluded (stored

execution

plan should be used).

In this case you need to store query text along with its plan name.
This will allow you to simply execute the plan each time a previously
parsed/planned query is executed.

However storing raw queries can be a *very* expensive operation, not
to mention the high cost of performing comparison on them. Due to the
associated cost, I'll
recommend using(and storing) hashes for query text.

If I were you, i'll write the hash calculation and storage and
retrieval functions in C and the top level function in Plpgsql.

Hope that helps.

regards,
-- Sibte

--
Sincerely,
Sergey Moroz

"Sergey Moroz" <smo@mgcp.com> writes:

So I wonder why doesn't postgres store hashes for all queries and
misses parsing step if not needed like Oracle does?

Because we think it's a waste of cycles, not to mention storage.

regards, tom lane