When encountering the error 38000: External Routine Exception from Postgres DB, follow these steps:
- Check PostgreSQL Logs: Immediately review the PostgreSQL log files. Look for any entries around the time the error was thrown. This can provide more context or a specific reason for the exception. The log file location depends on your PostgreSQL setup, but you can find it by running:
SHOW log_directory;
- Review Recent Changes: If the error started appearing after recent changes to the database (e.g., schema changes, new functions, or updated external extensions), revert those changes if possible, and see if the error persists.
- Inspect External Functions: Since this error relates to external routines (such as functions written in PL/Python, PL/Perl, etc.), identify and test these functions individually. You can find a list of external functions by querying the
pg_proc table and filtering by prolang, where prolang is not SQL, C, or internal languages. For example, for functions written in PL/Python: SELECT proname, prolang FROM pg_proc JOIN pg_language ON pg_proc.prolang = pg_language.oid WHERE lanname = 'plpythonu';
- Test External Function Dependencies: If the external functions rely on external libraries or systems, verify that these dependencies are correctly installed, accessible, and functioning as expected. This may involve checking system paths, environment variables, or network accessibility for remote dependencies.
- Check for Resource Constraints: Sometimes, external routines may fail due to resource limitations (e.g., memory, CPU). Check system and PostgreSQL resource usage (e.g., using
top, htop, or PostgreSQL's pg_stat_activity for active queries and pg_stat_statements for query statistics). Ensure there are enough resources available for the operation that caused the error. - Run a Database Consistency Check: Corrupted database files can sometimes cause strange errors. Run a consistency check using
pg_dump for a specific database or SELECT pg_catalog.pg_is_in_recovery(); to check if the database is in recovery mode, which may indicate issues. - Restart PostgreSQL Service: If all else fails and you cannot pinpoint the issue, consider safely restarting the PostgreSQL service. This can sometimes resolve transient issues related to external routines. Use:
sudo systemctl restart postgresql
- or the appropriate command for your system.
Each step is aimed at isolating and identifying the source of the 38000: External Routine Exception error. Proceed cautiously, especially with changes that may affect database integrity or availability.
