Обсуждение: Extension to rewrite queries before execution

On 08/13/2015 12:49 PM, Jeff Janes wrote:
> I am looking for an extension or a technique that will allow me to
> intercept a query by the exact query text, and replace that query with a
> different one.

What is sending the query?

In other words what library is the app using to communicate with the
Postgres server?

>
> The context is running a third-party app which issues queries I have no
> control over.  I'd like to intercept a specific query (which has no bind
> parameters) and either replace the query text with a different text
> which, for example, swaps out an "in list" clause to instead be an
> "exists (subquery)".
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into
> the extension source code and compile it, but of course something more
> flexible would be ideal.
>
> Thanks,
>
> Jeff


--
Adrian Klaver
adrian.klaver@aklaver.com

El 13/08/15 a las 17:37, Melvin Davidson escribió:
> You have not stated which Version or PostgreSQL,

He said it was for 9.4.

>> Is there anything out there like this?  This would be for 9.4.


--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Hi,

Le 13 août 2015 9:51 PM, "Jeff Janes" <jeff.janes@gmail.com> a écrit :
>
> I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one.
>
> The context is running a third-party app which issues queries I have no control over.  I'd like to intercept a specific query (which has no bind parameters) and either replace the query text with a different text which, for example, swaps out an "in list" clause to instead be an "exists (subquery)". 
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;" before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into the extension source code and compile it, but of course something more flexible would be ideal.
>

I know of no extension that does this, even if the idea is rather interesting. I don't remember if there is any hook that would allow such a thing. But that is definitely interesting.

--
Guillaume.

> I am looking for an extension or a technique that will allow me to
> intercept a query by the exact query text, and replace that query with a
> different one.
>
> The context is running a third-party app which issues queries I have no
> control over.  I'd like to intercept a specific query (which has no bind
> parameters) and either replace the query text with a different text which,
> for example, swaps out an "in list" clause to instead be an "exists
> (subquery)".
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into the
> extension source code and compile it, but of course something more flexible
> would be ideal.

I don't know if you are interested in using other than extensions, but
you could do it by hacking pgpool-II. It has built-in raw parser with
some functions to walk through the tree such as
raw_expression_tree_walker().  Also you could do the rewriting only
for particular applications (of course only if the application set
application name).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On 08/14/2015 08:56 AM, Jeff Janes wrote:
> On Thu, Aug 13, 2015 at 2:02 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 08/13/2015 12:49 PM, Jeff Janes wrote:
>
>         I am looking for an extension or a technique that will allow me to
>         intercept a query by the exact query text, and replace that
>         query with a
>         different one.
>
>
>     What is sending the query?
>
>     In other words what library is the app using to communicate with the
>     Postgres server?
>
>
> I believe it is some ancient version of Hibernate.
> Cheers,

So JDBC.

Don't know how much control you have over the setup, but this looks
interesting:

https://github.com/ttddyy/datasource-proxy

>
> Jeff


--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/13/2015 2:49 PM, Jeff Janes wrote:
> I am looking for an extension or a technique that will allow me to
> intercept a query by the exact query text, and replace that query with a
> different one.
>
> The context is running a third-party app which issues queries I have no
> control over.  I'd like to intercept a specific query (which has no bind
> parameters) and either replace the query text with a different text
> which, for example, swaps out an "in list" clause to instead be an
> "exists (subquery)".
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into
> the extension source code and compile it, but of course something more
> flexible would be ideal.
>
> Thanks,
>
> Jeff

Have you looked at pg_bouncer?  You'd have to make changes to the
source, but it's got a bunch of what you'd need.  It sits in the middle
and could swap out text pretty easy.  I'd guess.

-Andy

Hi, although I don't see what exactly you want to do,

> I am looking for an extension or a technique that will allow me to
> intercept a query by the exact query text, and replace that query with a
> different one.
>
> The context is running a third-party app which issues queries I have no
> control over.  I'd like to intercept a specific query (which has no bind
> parameters) and either replace the query text with a different text which,
> for example, swaps out an "in list" clause to instead be an "exists
> (subquery)".

I don't know such an extension but,

> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.

pg_hint_plan can do this.

http://osdn.jp/projects/pghintplan/

It can change guc parameters for specific queries but only during
planning time. So setting enable_* works as expected but work_mem
may not do exactly as expected.

LOAD 'pg_hint_plan';
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 (SELECT a, -a FROM generate_series(0, 9999) a);
CREATE INDEX ON t1 (a);
INSERT INTO hint_plan.hints VALUES (0, 'EXPLAIN SELECT * FROM t1 WHERE a = ?;', 'psql', 'set(enable_indexscan
off)set(enable_bitmapscanoff)'); 
ANALYZE t1;
SET pg_hint_plan.enable_hint_table TO yes;
EXPLAIN SELECT * FROM t1 WHERE a = 10;
<emits a plan using sequential scan ignoring index>
DELETE FROM hint_plan.hints;
EXPLAIN SELECT * FROM t1 WHERE a = 10;
<emits a plan using index scan>

'SeqScan(t1)' does effectively the same thing for the case.

As you see in the example above, EXPLAIN is not specially treated
so it is needed so that it affects the EXPLAIN query. Setting
pg_hint_plan.debug_print to 'detailed' would be useful to see
what string to be fed as 'normalized query'.

Of course it costs the time to search the hint table per one
query execution.

> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into the
> extension source code and compile it, but of course something more flexible
> would be ideal.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center