FrançaisGet started

Exceptions inside the database

The EXCEPTION section of a block: predefined exceptions, RAISE, OTHERS, propagation from block to block, and what the error cancels in the transaction.

  • 25 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • Predefined exception: what is it?

    Show the answer

    An error the database names itself, caught by name in a WHEN clause: division_by_zero, unique_violation, no_data_found, too_many_rows.

    Each name matches an SQLSTATE error code: unique_violation is code 23505, which the database ties to the message duplicate key value violates unique constraint.

  • What does this block show?

    ```sql
    DO $$
    BEGIN
    BEGIN
    PERFORM 1 / 0;
    RAISE NOTICE 'A';
    END;
    RAISE NOTICE 'B';
    EXCEPTION
    WHEN division_by_zero THEN
    RAISE NOTICE 'C';
    END $$;
    `

    • Only C
    • A then C
    • C then B
    • B then C
    Show the answer

    Only C

    The division fails before A: the inner block has no EXCEPTION section, so the error climbs to the outer block. Its WHEN clause prints C, and the outer block ends: B is never reached.

  • Error handlers go in the block's … section, each introduced by … followed by the exception's name.

    Show the answer

    Error handlers go in the block's EXCEPTION section, each introduced by WHEN followed by the exception's name.

    Example: EXCEPTION WHEN division_by_zero THEN RAISE NOTICE 'zero divisor';. The section is optional and goes before the block's END.

  • User-raised exception: what is it?

    Show the answer

    An error the code causes itself with RAISE EXCEPTION 'message', to refuse a situation the database does not consider faulty.

    An insufficient balance is not an error for the database: RAISE EXCEPTION 'Solde insuffisant : %', v_solde; makes it one. With no ERRCODE given, it is caught with raise_exception.

  • A transfer between two accounts fails now and then and nobody notices: no row written, no message. The block ends with EXCEPTION WHEN OTHERS THEN NULL;. Which fix applies?

    • Replace NULL with a COMMIT in the clause, to keep the writes
    • Name the expected errors, re-raise the others
    • Add an index on the accounts table to speed up the transfer
    • Switch the transaction to SERIALIZABLE to avoid conflicts
    Show the answer

    Name the expected errors, re-raise the others

    WHEN OTHERS THEN NULL swallows every error and undoes the block's writes without a word. You should name what you know how to handle and let the rest climb up, with RAISE; re-raising the caught error.

Study this deck