How to clean orphan temp schemas in Greenplum

Before running gpcheckcat, in maintenance window, run below scripts to check if Greenplum has orphan temp schema on master or segments.
The goal is to save time for gpcheckcat, especially for large clusters.

0. For master and all segments:

This option can drop temp schemas for master and all segments, if you go for this option, you can skip below 2 options.

psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a}; done

Sample output:

check for postgres
check for template1
check for gpperfmon
check for testdb
drop schema if exists pg_temp_316 cascade;
drop schema if exists pg_temp_3 cascade;

1. For master:

psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'drop schema ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo;" ${a}; done

Sample output:

check for postgres
check for template1
check for gpperfmon
check for testdb
drop schema pg_temp_316 cascade;

2. For segments:

psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'PGOPTIONS=''-c gp_session_role=utility'' psql -h ' || g.hostname || ' -p '||g.port || ' -c ' || '''drop schema '||p.nspname||' cascade;'' ${a}' from gp_dist_random('pg_namespace') p,gp_segment_configuration g where p.nspname like 'pg_temp%' and g.role='p' and g.content=p.gp_segment_id and p.nspname not in (select 'pg_temp_' || sess_id::varchar from pg_stat_activity);" ${a}; done

Sample output:

check for postgres
check for template1
check for gpperfmon
check for testdb
PGOPTIONS='-c gp_session_role=utility' psql -h sdw6 -p 40070 -c 'drop schema pg_temp_3 cascade;' testdb