Thread: How to inspect tuples during execution of a plan?

How to inspect tuples during execution of a plan?

From
Ernst-Georg Schmid
Date:
Hello all,

I'd like to inspect the content of tuples as they are sent during the
execution of a query in order to react to their values.

I guess I could do it with a FDW, but that's a bit clumsy so I took a
look at the hooks but have two questions:

1.) Would ExecutorRun_hook be the correct place to implement such an
'tuple inspector'?
2.) If yes, how? As far as I understand the source code, I would have
to provide my own implementation based on standard_ExecutorRun and
replace the ExecutePlan function with my own one that takes a look at
each 'slot' like so

if (estate->es_junkFilter != NULL)1589             slot = ExecFilterJunk(estate->es_junkFilter, slot);1590

Tuple inspection here
1591         /*1592          * If we are supposed to send the tuple somewhere, do so. (In1593          * practice, this
isprobably always the case at this point.)1594          */1595         if (sendTuples)1596         {
 

If there is a better way, please advise. I'm really a newbie to this.

Best regards,

Ernst-Georg Schmid



Re: How to inspect tuples during execution of a plan?

From
Ashutosh Bapat
Date:
On Mon, Oct 10, 2016 at 1:39 PM, Ernst-Georg Schmid
<ernst.georg.schmid@googlemail.com> wrote:
> Hello all,
>
> I'd like to inspect the content of tuples as they are sent during the
> execution of a query in order to react to their values.

The correct answer will depend upon the purpose of this inspection.
You may write a function, which can be invoked through a query
wrapping the original query OR you may add a new planner code (check
custom scans) which does the inspection. These are many ways to do
this with different degrees of invasive-ness. They may or may not
suite your purpose.

>
> I guess I could do it with a FDW, but that's a bit clumsy so I took a
> look at the hooks but have two questions:
>
> 1.) Would ExecutorRun_hook be the correct place to implement such an
> 'tuple inspector'?
> 2.) If yes, how? As far as I understand the source code, I would have
> to provide my own implementation based on standard_ExecutorRun and
> replace the ExecutePlan function with my own one that takes a look at
> each 'slot' like so
>
> if (estate->es_junkFilter != NULL)
>  1589             slot = ExecFilterJunk(estate->es_junkFilter, slot);
>  1590
>
> Tuple inspection here
>
>  1591         /*
>  1592          * If we are supposed to send the tuple somewhere, do so. (In
>  1593          * practice, this is probably always the case at this point.)
>  1594          */
>  1595         if (sendTuples)
>  1596         {
>
> If there is a better way, please advise. I'm really a newbie to this.

Either of those would do, if you want to write change the executor.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company



Re: How to inspect tuples during execution of a plan?

From
Ernst-Georg Schmid
Date:
Hello,

>
> Either of those would do, if you want to write change the executor.
>

I used the ExeceutorRun_hook and it seems to work. The drawback is,
that I have to provide my own implementation of ExecutePlan() which
could make it incompatible over versions of PostgreSQL. I don't know
how stable that function is over time.

https://github.com/ergo70/pg_sentinel

Thank you,

Ernst-Georg



Re: How to inspect tuples during execution of a plan?

From
Jim Nasby
Date:
On 10/12/16 2:58 AM, Ernst-Georg Schmid wrote:
>> Either of those would do, if you want to write change the executor.
>> >
> I used the ExeceutorRun_hook and it seems to work. The drawback is,
> that I have to provide my own implementation of ExecutePlan() which
> could make it incompatible over versions of PostgreSQL. I don't know
> how stable that function is over time.

There might be another possibility, which would be to use one of the 
earlier hooks to change the query destination, and to implement a custom 
receiver.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461