Re: Transient plans versus the SPI API

On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote:
> On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:

> > Hm, you mean reverse-engineering the parameterization of the query?
>
> Yes, basically re-generate the query after (or while) parsing, replacing
> constants and arguments with another set of generated arguments and
> printing the list of these arguments at the end. It may be easiest to do
> This in parallel with parsing.
>
> > Interesting thought, but I really don't see a way to make it practical.
>
> Another place where this could be really useful is logging & monitoring
>
> If there were an option to log the above queries as
>
> "SELECT a, b FROM foo WHERE c = $1", (123)
> "SELECT a, b FROM foo WHERE c = $1", (97)
> "SELECT a, b FROM foo WHERE c = $1", (236)

The main monitoring use_case would be pg_stat_statements,
http://developer.postgresql.org/pgdocs/postgres/pgstatstatements.html
which is currently pretty useless for queries without parameters

> it would make all kinds of general performance monitoring tasks also
> much easier, not to mention that this forw would actually be something
> that kan be cached internally.
>
> For some users this might even be worth to use this feature alone,
> without it providing Repeating Plan Recognition.
>
> > In any case, it would amount to making up for a bad decision on the
> > application side, ie, not transmitting the query in the parameterized
> > form that presumably exists somewhere in the application. I think
> > we'd be better served all around by encouraging app developers to rely
> > more heavily on parameterized queries ... but first we have to fix the
> > performance risks there.
> >
> > regards, tom lane
> >
>
>
>