Re: Cached Query Plans (was: global prepared statements)

From: PFC <lists(at)peufeu(dot)com>
To: "Bruce Momjian" <bruce(at)momjian(dot)us>
Cc: "Csaba Nagy" <nagy(at)ecircle-ag(dot)com>, "Dawid Kuroczko" <qnex42(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Cached Query Plans (was: global prepared statements)
Date: 2008-04-14 14:51:10
Message-ID: op.t9lvjkwwcigqcu@apollo13.peufeu.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


>> Bind message behaviour was modified :
>> - If the user asks for execution of a named prepared statement, and the
>> named statement does not exist in PG's prepared statements cache,
>> instead
>> of issuing an error and borking the transaction, it Binds to an empty
>> statement, that takes no parameters, and returns no result. Parameters
>> sent by the user are consumed but not used.
>
> You mentioned the need for a wire protocol change to allow this. Why
> can't this be controlled with a server variable, like SET auto_prepare =
> 'true'?

Actually, thanks to the hack, the wire protocol doesn't change.
Explanation :

- Send Parse(SQL) to unnamed statement + Bind unnamed statement => works
as usual (no cache)
- Send only Bind (named statement) with a statement name that is not found
in the cache => doesn't raise an error, instead informs the application
that the statement does not exist. The application can then prepare (send
a Parse message with SQL and a name) the statement and give it a name. I
used as name the SQL itself, but you can use anything else. The
application can then send the Bind again, which will (hopefully) work.

So, here, the information ("cache" or "don't cache") is passed from the
client to the server, in a hidden way : it depends on what function you
use to send the query (unnamed statements are not cached, named statements
are cached).
There is no protocol change, but a new information is provided to the
server nonetheless.

Downside to this is that the application needs to be modified (only a
little, though) and applications that expect exceptions on "Statement does
not exist" will break, thus the necessity of a GUC to control it.

It was just a quick & dirty test to see if this way of doing it was an
option to consider or not. Apparently it works, but wether it is The Right
Way remains to be seen...

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Mielke 2008-04-14 14:55:58 Re: Cached Query Plans
Previous Message Tom Lane 2008-04-14 14:25:54 Race conditions in relcache load (again)