Re: Skip temporary table schema name from explain-verbose output.

On Tue, Apr 27, 2021 at 11:07 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> On Tue, Apr 27, 2021 at 10:51 AM Amul Sul <sulamul(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > Temporary tables usually gets a unique schema name, see this:
> >
> > postgres=# create temp table foo(i int);
> > CREATE TABLE
> > postgres=# explain verbose select * from foo;
> > QUERY PLAN
> > -----------------------------------------------------------------
> > Seq Scan on pg_temp_3.foo (cost=0.00..35.50 rows=2550 width=4)
> > Output: i
> > (2 rows)
> >
> > The problem is that explain-verbose regression test output becomes
> > unstable when several concurrently running tests operate on temporary
> > tables.
> >
> > I was wondering can we simply skip the temporary schema name from the
> > explain-verbose output or place the "pg_temp" schema name?
> >
> > Thoughts/Suggestions?
>
> How about using an explain filter to replace the unstable text
> pg_temp_3 to pg_temp_N instead of changing it in the core? Following
> are the existing explain filters: explain_filter,
> explain_parallel_append, explain_analyze_without_memory,
> explain_resultcache, explain_parallel_sort_stats, explain_sq_limit.
>

Well, yes eventually, that will be the kludge. I was wondering if that
table is accessible in a query via pg_temp schema then why should
bother about printing the pg_temp_N schema name which is an internal
purpose.

> Looks like some of the test cases already replace pg_temp_nn with pg_temp:
> -- \dx+ would expose a variable pg_temp_nn schema name, so we can't use it here
> select regexp_replace(pg_describe_object(classid, objid, objsubid),
> 'pg_temp_\d+', 'pg_temp', 'g') as "Object description"
>

This \d could be one example of why not simply show pg_temp instead of
pg_temp_N.

Regards,
Amul