Creating the error_log Table for Automatic Error Logging

Imagine you’re working with a database, processing a bunch of transactions, and suddenly something goes wrong. The user complains, the business is freaking out, and you have no clue what exactly happened. That’s where proper error logging comes in. It lets you:

• Store errors in the database for later analysis.
• Easily recover error details: when it happened, the message, and related data.
• Make your code better by working on common mistakes you spot in the logs.

Basically, logging is like having a security camera for your database: you see everything that went wrong and when it happened.

Let’s Create the error_log Table

Let’s start with the basics. We need to create a table where we’ll store info about errors. The table will have these fields:

• id — unique error identifier.
• error_message — error text.
• error_time — when the error happened.
• (Optional) context — error context, if you want to store extra details.

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,        -- Unique error identifier
    error_message TEXT NOT NULL,  -- Error message text
    error_time TIMESTAMP NOT NULL DEFAULT NOW(), -- When the error happened
    context JSONB                 -- Extra error data
);

What’s going on here?

• id SERIAL PRIMARY KEY — this field automatically creates a unique ID for every record.
• error_message TEXT NOT NULL — this is where the error text goes. It’s required.
• error_time TIMESTAMP NOT NULL DEFAULT NOW() — this field logs when the event happened. If you don’t provide a value, it’ll use the current time thanks to DEFAULT NOW().
• context JSONB — optional, for storing extra info, like data about the operation where the error happened.

After you run the CREATE TABLE command, your database will have a structure for storing logs.

Example: Writing Errors to the Table

Now that the table’s ready, let’s get practical: we’ll save error info into the table. We’ll write a function that logs the error text and time into error_log.

Sample Error Logging Function

CREATE OR REPLACE FUNCTION log_error(p_error_message TEXT, p_context JSONB DEFAULT NULL)
RETURNS VOID AS $$
BEGIN
    INSERT INTO error_log (error_message, context)
    VALUES (p_error_message, p_context);
END;
$$ LANGUAGE plpgsql;

1. p_error_message — input parameter for the error text.
2. p_context — optional parameter for extra data. Defaults to NULL.
3. INSERT INTO error_log (error_message, context) — adds a new record to the error_log table.
4. DEFAULT NULL — if context isn’t set, the field is NULL.

Now, if you want to log an error message, you can call the function like this:

SELECT log_error('Error while executing query', '{"query": "SELECT * FROM data"}');

This will add a new record to the error_log table.

Automatic Error Logging

Typing SELECT log_error(...) every time by hand is kinda annoying. Let’s automate this using exception handling.

Sample function with exception handling.

CREATE OR REPLACE FUNCTION divide_numbers(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    result NUMERIC;
BEGIN
    -- Let’s try to do the division
    result := a / b;

    -- Return the result
    RETURN result;
EXCEPTION
    WHEN division_by_zero THEN
        -- Log the error and pass the context
        PERFORM log_error('Division by zero', jsonb_build_object('a', a, 'b', b));
        -- Raise an exception for the user
        RAISE EXCEPTION 'Division of % by % is not possible — divisor is zero', a, b;
END;
$$ LANGUAGE plpgsql;

1. Division check: does result := a / b. If b = 0, PostgreSQL throws a division_by_zero exception.
2. Exception handler (EXCEPTION) catches the error.
3. Inside the handler, we call log_error to write the error to the table along with the parameters that caused the problem.
4. After logging the error, RAISE EXCEPTION throws a new error message so the user gets notified too.

Example call:

SELECT divide_numbers(10, 0);

Result:

• The user will see the error: Division of 10 by 0 is not possible — divisor is zero.
• The error_log table will have a record about the division by zero.

2

Task

SQL SELF, level 52, lesson 2

Locked

Inserting a record with an error into the `error_log` table

Inserting a record with an error into the `error_log` table

Open