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

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.

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"

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com