Re: Planner debug views

On Tue, Jul 28, 2015 at 6:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Another point is that we decided a long time ago that EXPLAIN's plain-text
> output format is not intended to be machine-parsable, and so objecting to
> a design on the grounds that it makes machine parsing harder is pretty
> wrongheaded. I'd think there is plenty of room for dropping in additional
> output data in the non-text output formats.

I think this will work, for example, I can put several sections of the
JSON output:

{
"plan": {
// original EXPLAIN plan tree sits here
},
"paths":{
// paths considered sits here
}
// ...
}

But still, it requires an extra step for user: he will needs to
programming to read through output (easier) and persists into a table for
later query.

Can we simplify above with foreign table methods? There are two major
concerns about this method per previous discussions: security and
usability. I think the main cause is the sharing foreign table design. How
about we put foreign table in separate pg_stat_tmp/<pid> folders, similar
to what Alvaro proposes, and similar to /proc file system. Meanwhile, we
introduce a function to help user create foreign table mapping to these
files. This looks solves the security and usability issues to me:

postgres=# select pg_debug_planner_init();
Foreign table 'pg_planner_rels', 'pg_planner_paths' created.
postgres=# EXPLAIN (debug_planner=on, ...) ...
...
postgres=# select * from pg_planner_paths;
...

Thoughts?

Qngqing