Graceful exception handling

Transactions and Error Handling in PostgreSQL

Jason Myers

Principal Engineer

Graceful degradation in action

DO $$
BEGIN
    UPDATE cookies SET quantity = quantity-6 WHERE name = 'Linga';
EXCEPTION 
    WHEN check_violation THEN
        INSERT INTO errors (msg) values ('Quantity can not be less than 0');
        UPDATE cookies SET quantity = 0 WHERE name = 'Linga';
        INSERT INTO errors (msg) values ('Set quantity to the 0 for Linga.');
END$$;

When to use graceful degradation

Loading data from an external system where you want to replace nulls with 0s
Getting readings from an instrument that is only accurate up to a certain threshold
Receiving dates that are out of bounds that you want to set to some sentinel value
Writing all records that cause exceptions to another table for further processing

When to consider using graceful exception handling

When the new value would be hidden behind a math operation such as a sum, avg, or other aggregate.
When the new value affects data in a time series.

Let's practice!

Transactions and Error Handling in PostgreSQL