Rollback Transaction on Trigger ERROR

Question

I'm trying to check if the room that is going to be inserted in the system is already rented at that date or not. I've though about counting the rows that match both the room number and the date, and then rolling back the transaction. But I'm getting the following error, even though I have changed the code to raise user-defined exceptions:

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "checkRoom"() line 17 at SQL statement

CREATE OR REPLACE FUNCTION "checkRoom"() RETURNS TRIGGER AS
$BODY$
DECLARE 
    counter integer;
  BEGIN
    SELECT COUNT("num_sesion")
    FROM "Sesion"
    INTO counter
    WHERE "Room_Name"=NEW."Room_Name" AND "Date"=NEW."Date";

    IF (counter> 0) THEN -- Probably counter>1 as it's triggered after the transaction..
        raise notice 'THERE'S A ROOM ALREADY!!';
        raise exception 'The room is rented at that date';
    END IF;
    RETURN new;
EXCEPTION
    WHEN raise_exception THEN
        ROLLBACK TRANSACTION;
        RETURN new;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;

Then I create the trigger:

CREATE TRIGGER "roomOcupied" AFTER INSERT OR UPDATE OF "Room_Name", "Date"
ON "Sesion" FOR EACH ROW
EXECUTE PROCEDURE "checkRoom"();

It's been 2 years from my last approach to SQL and the changes between plsql and plpgsql are getting me crazy.

Answer 1

PostgreSQL processes errors significantly differently from other databases. Any unhandled errors are raised to the user. Inside PL/pgSQL you can trap any exception or you can raise any exception, but you cannot explicitly control transactions. Any PostgreSQL statement is executed inside of a transaction (functions too). And the most outer transaction is automatically broken when any unhandled exception goes to the top.

What you can:

• raise exception (often in triggers)

BEGIN
  IF CURRENT_USER <> 'Admin' THEN
    RAISE EXCEPTION 'missing admin rights';
  END IF;
  RETURN NEW;
END;

• trapping exception

BEGIN
  BEGIN -- start of protected section
    -- do some, what can be stopped by exception
  EXCEPTION WHEN divide_by_zero THEN
    -- exception handler;
    RAISE WARNING 'I was here';
    -- should ignore
  EXCEPTION WHEN others THEN
    -- any unexpected exception
    RAISE WARNING 'some unexpected issue';
    RAISE; -- forward exception'
  END;

There is no other possibility - so writing application in PL/pgSQL is very simple, but different than PL/SQL or TSQL.

Answer 2

A couple of issues with your trigger function:

• Use IF EXISTS (...) THEN instead of counting all occurrences. Faster, simpler. See:

  • PL/pgSQL checking if a row exists

• A trigger function AFTER INSERT OR UPDATE can just return NULL. RETURN NEW is only relevant for triggers called BEFORE. The manual:

  The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

• Unbalanced single quote.

• As @Pavel explained, you cannot control transactions from within a plpgsql function. Any unhandled exception forces your entire transaction to be rolled back automatically. So, just remove the EXCEPTION block.

Your hypothetical trigger rewritten:

CREATE OR REPLACE FUNCTION check_room()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF EXISTS (
         SELECT FROM "Sesion"    -- are you sure it's not "Session"?
         WHERE  "Room_Name" = NEW."Room_Name"
         AND    "Date" = NEW."Date") THEN
     RAISE EXCEPTION 'The room is rented at that date';
   END IF;
   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

A BEFORE trigger makes more sense.

But a UNIQUE INDEX ON ("Room_Name", "Date") would do the same, more efficiently. Then, any row in violation raises a duplicate key exception and rolls back the transaction (unless caught and handled). In modern Postgres you can alternatively skip or divert such INSERT attempts with INSERT ... ON CONFLICT .... See:

• How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Advanced usage:

• How to use RETURNING with ON CONFLICT in PostgreSQL?