Thread: Extension to rewrite queries before execution

Extension to rewrite queries before execution

From

Jeff Janes

Date:

13 August 2015, 22:49:23

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

Re: Extension to rewrite queries before execution

From

Melvin Davidson

Date:

13 August 2015, 23:37:52

You have not stated which Version or PostgreSQL, nor the O/S involved. That being said, depending on what the specific query is, you might consider using a Rule or Trigger to handle it. If you use a Trigger ( which is the preferred method) you can also embed "set" commands the associated function.

On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes <jeff.janes@gmail.com> 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

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Extension to rewrite queries before execution

From

Adrian Klaver

Date:

14 August 2015, 00:02:03

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

Re: Extension to rewrite queries before execution

From

Martín Marqués

Date:

14 August 2015, 03:40:47

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

Re: Extension to rewrite queries before execution

From

Guillaume Lelarge

Date:

14 August 2015, 09:04:13

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.

Re: Extension to rewrite queries before execution

From

Tatsuo Ishii

Date:

14 August 2015, 09:34:46

> 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

Re: Extension to rewrite queries before execution

From

Jeff Janes

Date:

14 August 2015, 18:53:41

> On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes <jeff.janes@gmail.com> 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.

You have not stated which Version or PostgreSQL, nor the O/S involved. That being said, depending on what the specific query is, you might consider using a Rule or Trigger to handle it. If you use a Trigger ( which is the preferred method) you can also embed "set" commands the associated function.

Sure I did, 9.4. OS would be Linux, although I would hope a solution would be work. If you mean the built-in-to-core rules or triggers, those wouldn't work. Neither one allows you to rewrite a where clause as far as I can tell. Rules allows you add one, but not more than that. And triggers don't exists for select queries.

Cheers,

Jeff

Re: Extension to rewrite queries before execution

From

Jeff Janes

Date:

14 August 2015, 18:57:05

On Thu, Aug 13, 2015 at 2:02 PM, Adrian Klaver <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,

Jeff

Re: Extension to rewrite queries before execution

From

Adrian Klaver

Date:

14 August 2015, 19:11:20

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

Re: Extension to rewrite queries before execution

From

Melvin Davidson

Date:

14 August 2015, 19:18:27

Jeff, although it would be tedious, you could write a trigger of the form

BEFORE INSERT ON yatta_yatta

EXECUTE PROCEDURE check_rewrite;

Then in check_rewrite, you could examine the contents of pg_stat_activity.query column for the current process

and act accordingly.

I acknowledge that it will impact performance, but I see no other way to accomplish what you wish.

At the very least, you would be able to issue the "set enable_* =off;" or "set work_mem=*; inside the procedure.

That is the best I can suggest.

On Fri, Aug 14, 2015 at 11:53 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

> On Thu, Aug 13, 2015 at 1:37 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Thu, Aug 13, 2015 at 3:49 PM, Jeff Janes <jeff.janes@gmail.com> 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.

You have not stated which Version or PostgreSQL, nor the O/S involved. That being said, depending on what the specific query is, you might consider using a Rule or Trigger to handle it. If you use a Trigger ( which is the preferred method) you can also embed "set" commands the associated function.

Sure I did, 9.4. OS would be Linux, although I would hope a solution would be work. If you mean the built-in-to-core rules or triggers, those wouldn't work. Neither one allows you to rewrite a where clause as far as I can tell. Rules allows you add one, but not more than that. And triggers don't exists for select queries.

Cheers,

Jeff

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Extension to rewrite queries before execution

From

Andy Colson

Date:

15 August 2015, 00:50:41

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

Re: Extension to rewrite queries before execution

From

Kyotaro HORIGUCHI

Date:

17 August 2015, 09:11:21

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