Using plpgsql_check to Find Compilation Errors and Profile Functions

plpgsql_checkThere is always a need for profiling tools in databases for admins or developers. While it is easy to understand the point where an SQL is spending more time using EXPLAIN or EXPLAIN ANALYZE in PostgreSQL, the same would not work for functions. Recently, Jobin has published a blog post where he demonstrated how plprofiler can be useful in profiling functions. plprofiler builds call graphs and creates flame graphs which make the report very easy to understand. Similarly, there is another interesting project called plpgsql_check which can be used for a similar purpose as plprofiler, while it also looks at code and points out compilation errors. Let us see all of that in action, in this blog post.

Installing plpgsql-check

You could use yum on RedHat/CentOS to install this extension from PGDG repository. Steps to perform source installation on Ubuntu/Debian are also mentioned in the following logs.

On RedHat/CentOS

On Ubuntu/Debian

Creating and enabling this extension

There are 3 advantages of using plpgsql_check

  1. Checking for compilation errors in a function code
  2. Finding dependencies in functions
  3. Profiling functions

When using plpgsql_check for the first 2 requirements, you may not need to add any entry to shared_preload_libraries. However, if you need to use it for profiling functions (3), then you should add appropriate entries to shared_preload_libraries so that it could load both plpgsql and plpgsql_check. Due to dependencies, plpgsql must be before plpgsql_check in the shared_preload_libraries config as you see in the following example :

Any change to shared_preload_libraries requires a restart. You may see the following error when you do not have plpgsql before plpgsql_check in the shared_preload_libraries config.

Once the PostgreSQL instance is started, create this extension in the database where you need to perform any of the previously discussed 3 tasks.

Finding Compilation Errors

As discussed earlier, this extension can help developers and admins determine compilation errors. But why is it needed? Let’s consider the following example where we see no errors while creating the function. By the way, I have taken this example from my previous blog post where I was talking about Automatic Index recommendations using hypopg and pg_qualstats. You might want to read that blog post to understand the logic of the following function.

From the above log, it has created the function with no errors. Unless we call the above function, we do not know if it has any compilation errors. Surprisingly, with this extension, we can use the plpgsql_check_function_tb() function to learn whether there are any errors, without actually calling it.