Re: Extension to rewrite queries before execution

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_bitmapscan off)');
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