Orphaned temporary schemas in Greenplum Database
Article ID: 295662
Updated On:
Products
Issue/Introduction
Each database session may create and use a temporary schema for handling session temporary tables. Usually, these schemas are dropped at the end of the session. However, because of a process crash or other non-default session finishes, these temporary schemas continue to exist after the session has finished.
These temporary schemas do not usually create trouble except when transaction management is involved and a catalog check is done.
Note: Transaction management in this case involves tracking the transaction age for transaction wraparound prevention.
During transaction management, these temporary schemas (and objects within them) are session specific. As a result, VACUUM commands can not process them.
During a catalog check, they provide a lot of noise in the gpcheckcat log file and can mask other important catalog inconsistencies.
It is recommended that these 'orphan' temporary schemas are dropped, especially before performing a catalog check using gpcheckcat. The gpcheckcat utility will recognize them and point to them, but it is easier and faster to get rid of these orphan temporary schemas before running the tool.
Note: It is recommended for the database to be in the admin mode (no user sessions), so these orphan schemas can be identified easily.
Environment
Resolution
The following commands will create scripts to clean up orphan temporary schemas on master and segment instances. The commands are taken into consideration while running the sessions, so these commands will work with the existing workload.
1. Identify the orphan temporary schemas and generate scripts to drop them using the following command:
cat /dev/null > execute_drop_on_all.sh | psql -d template1 -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "Checking database ${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} > drop_temp_schema_$a.ddl ; echo "psql -f drop_temp_schema_$a.ddl -d ${a}" >> execute_drop_on_all.sh ; done
The above script will create a .sql file for each database in the current directory, which contains commands to drop the orphan temp. schemas.
Check the contents of all the generated scripts before executing them.
2. Execute the scripts generated in the previous step to drop the orphan schemas.
chmod +x execute_drop_on_all.sh nohup ./execute_drop_on_all.sh | tee execute_drop_on_all.op 2>&1
The above script contains the commands to execute the DDL files generated during step 1.
After executing step 2, there should be no orphan schema's in the database. You can verify this by executing step 1 again and inspecting the .sql files.
Feedback
