Thread: parsed queries (cursors) cashing issues

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?

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).




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




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