How to trigger an alert when a Postgres PL/pgSQL exception occurs

Introduction

There might be situations where you or a group of people wants to get notified in case something goes wrong in any of your long-running PL/pgSQL code. This short article demonstrates how Azure Monitor can be leveraged for this kind of task.

Configuration

First, we'll have to forward the server logs to a Logs Analytics workspace, which we then use to create an Azure Monitor Alert. So let's create a workspace in case there is not one in place yet.

Create a Log Analytics Workspace

There is nothing exciting about that. Just navigate to the Azure Portal, enter Log Analytics workspaces in the search bar, and follow the wizard.

Next, we'll want to forward the Postgres Server Logs to the Log Analytics workspace.

Configure Diagnostic settings

Navigate to Monitoring > Diagnostic settings and click Add diagnostic setting. Make sure PostgreSQL Server Logs is checked in the Logs section to the left. Select the Log Analytics workspace from the dropdown list and hit save.

🔎 In case you are running Postgres Single Server a pricing tier of General Purpose or Memory Optimized is required so that you are able to forward the PostgreSQL Server Logs to a Logs Analytics Workspace.

Before we now get to create the alert, let's first examine some example PL/pgSQL code snippets.

Example PL/pgSQL function

The following function is flawed in that it attempts to divide by zero which throws an exception.

create or replace function bad_function() returns void as
$$
declare
    l_sqlstate          text;
    l_message           text;
    l_exception_context text;
    l_context           text;
begin
    select 1 / 0;
exception
    when division_by_zero then
        get stacked diagnostics
            l_sqlstate = returned_sqlstate,
            l_message = message_text,
            l_exception_context = pg_exception_context,
            l_context = pg_context;

        select exception_logger(l_sqlstate, l_message, l_exception_context, l_context);
end;
$$ language plpgsql;

The example makes use of the GET STACKED DIAGNOSTICS command to get detailed information about the current exception and passes it on to a simple logger function.

create or replace function exception_logger(p_sqlstate text, p_message text, p_exception_context text, p_context text) returns void as
$$
begin
    raise exception E'Exception occured
        state  : %
        message: %
        context: %
        stack  : %' ,p_sqlstate, p_message, p_exception_context, p_context;
end
$$ language plpgsql;

This dedicated logger function helps standardize the error message that we later parse.

[2022-09-12 15:56:46] [P0001] ERROR: Exception occured
[2022-09-12 15:56:46] state  : 22012
[2022-09-12 15:56:46] message: division by zero
[2022-09-12 15:56:46] context: SQL statement "select 1 / 0"
[2022-09-12 15:56:46] PL/pgSQL function bad_function() line 8 at SQL statement
[2022-09-12 15:56:46] stack  : PL/pgSQL function bad_function() line 11 at GET STACKED DIAGNOSTICS
[2022-09-12 15:56:46] Where: PL/pgSQL function logger(text,text,text,text) line 3 at RAISE
[2022-09-12 15:56:46] SQL statement "select logger(l_sqlstate, l_message, l_exception_context, l_context)"
[2022-09-12 15:56:46] PL/pgSQL function bad_function() line 17 at SQL statement

Creating an alert

Now that we know what an exception message will look like, we can create an alert from the Postgres blade by navigating to Monitoring > Alerts. First, create an alert rule and select Custom log search as the source signal.

When asked for a KQL query, enter the following and click on Continue Editing Alert. The query returns every row from the table AzureDiagnostics where the Log-Message contains the keyword Exception and belongs to the category PostgreSQLLogs.

AzureDiagnostics
| where Category == "PostgreSQLLogs" and Message has "Exception"
| project TimeGenerated, Message
| order by TimeGenerated desc

From within the Measurement section, choose an Aggregation granularity that fits your purpose.

In the example from above, if e.g. 2 events occur within 1 minute the aggregated result will be 2 and passed on to the Alert Logic. Next, the logic defines when an alert should be fired. The example below will fire an alert, whenever a single exception occurs within 1 minute.

Don't forget to create an action group that defines notification types.

And with that, every exception will trigger an Azure Alert. You can find an example below. Unfortunately, the exception message does not get shown in the e-mail. However, by clicking on the Search Results link we will be forwarded to the Log Analytics workspace pointing to the event.

Conclusion

This short walkthrough demonstrated how we can easily receive Azure Alerts triggered by your PL/pgSQL code. Let me summarize the most important points.

• When using a single server, a pricing tier of General Purpose or Memory Optimized is required
• Be patient when looking for the Postgres Server Logs in your Log Analytics Workspace, it will take some time (~ 5 minutes) until they show up
• The log type is AzureDiagnostics, and the category PostgreSQLLogs
• We can't directly propagate the exception message via the alert mail. Instead, we need to click the Search results link from the mail

I hope you found this post informative! 🔎 Happy hacking! 👨🏻‍💻

Further reading