Re: Extension to rewrite queries before execution - Mailing list pgsql-general

From Melvin Davidson
Subject Re: Extension to rewrite queries before execution
Date
Msg-id CANu8Fixa=+cqKFc7aM4RAxOyBJAKVAOxK-7pfCZkpOuBfDkQ8w@mail.gmail.com
Whole thread Raw
In response to Re: Extension to rewrite queries before execution  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Extension to rewrite queries before execution
Next
From: Chris Mair
Date:
Subject: Re: stack depth