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