Troubleshooting
Problem
When running the support script to find the dependencies between tables and views (/nz/support/bin/nz_view_references) you may see this error: "ERROR: SET SCHEMA: is not allowed in this mode".
Symptom
We can not get dependency information between tables and views while running nz_view_reference, if NPS version is 7.0.3 or later and enable_schema_dbo_check in /nz/data/postgresql.conf is set 0 (i.e. multiple schema support is turned off).
Cause
This is a slight mistake in "nz_view_references" where it checks SCHEMA SUPPORT feature. The script does not check the SCHEMAS_ARE_BEING_USED variable set in the CODE_basic_connection script.
Diagnosing The Problem
nz_view_references calls another script (/nz/support/bin/lib/CODE_basic_connection), which checks if the schemas are being used and behaves differently if they are. Unfortunately nz_view_references is not checking correctly the variables CODE_basic_connection is setting, and missing that multiple schema support is off. This leads it to trying to execute the SET SCHEMA command, and the error is correctly returned.
Resolving The Problem
There are two solutions for this error. You could enable enable_schema_dbo_check in /nz/data/postgresql.conf, but this is a big change just to get a utility script to run.
A better fix is to correct the code in /nz/support/bin/nz_view_references to check all the correct variables.
Before modification (lines 598 to 607):
================================
if [ "$the_passed_schema" = "DEFINITION_SCHEMA" -o "$the_passed_schema" = "definition_schema" ]; then unset NZ_SCHEMA
elif [ "$the_passed_schema" = "INFORMATION_SCHEMA" -o "$the_passed_schema" = "information_schema" ]; then unset NZ_SCHEMA
elif [ "$the_passed_schema" != "" ]; then export NZ_SCHEMA=\"${the_passed_schema}\"
else
if [ "$FULL_SCHEMA_SUPPORT" ]; then export NZ_SCHEMA=\"`nz_get_schema_name`\"
else unset NZ_SCHEMA
fi
fi
After modification (lines 598 to 607):
=============================
if [ "$the_passed_schema" = "DEFINITION_SCHEMA" -o "$the_passed_schema" = "definition_schema" ]; then unset NZ_SCHEMA
elif [ "$the_passed_schema" = "INFORMATION_SCHEMA" -o "$the_passed_schema" = "information_schema" ]; then unset NZ_SCHEMA
elif [ "$the_passed_schema" != "" ]; then export NZ_SCHEMA=\"${the_passed_schema}\"
else
if [ "$FULL_SCHEMA_SUPPORT" -a $SCHEMAS_ARE_BEING_USED -eq TRUE];
then export NZ_SCHEMA=\"`nz_get_schema_name`\"
else unset NZ_SCHEMA
fi
fi
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21688941