Postgres RAISE EXCEPTION does not abort function execution-CodePudding

I wrote the below function:

CREATE OR REPLACE FUNCTION process_init_transfer(
  holder_email TEXT,
  order_key BIGINT, ticket_key BIGINT,
  email_address TEXT,
  first_name TEXT, last_name TEXT
) RETURNS TEXT AS $$
  DECLARE
    _id BIGINT;
    _token TEXT;
    _title TEXT;
    _tickets_count INTEGER;
    _transfers_count INTEGER;
  BEGIN
    SELECT
      COUNT(*), events.name
    INTO
      _tickets_count, _title
    FROM
      tickets, purchases, ticket_books, events
    WHERE
      purchases.email = holder_email AND
      purchases.id = order_key AND
      tickets.order_id = purchases.id AND
      tickets.id = ticket_key AND
      ticket_books.id = tickets.book_id AND
      events.id = ticket_books.event_id
    GROUP BY
      events.name;
    IF _tickets_count <> 1 THEN
      RAISE EXCEPTION 'Error finding your ticket, contact support';
    END IF;
    SELECT
      COUNT(*)
    INTO
      _transfers_count
    FROM
      ticket_transfers
    WHERE
      ticket_id = ticket_key AND
      (status = 'PENDING' OR status = 'ACCEPTED' OR status = 'CONFIRMED');
    IF _transfers_count > 0 THEN
      RAISE EXCEPTION 'Ticket already transfered, contact support if you believe this is an error';
    END IF;

    INSERT INTO
      ticket_transfers (ticket_id, email, name, surname, token)
    VALUES
      (ticket_key, email_address, first_name, last_name, random_string(16))
    RETURNING
      token INTO _token;

    INSERT INTO
      emails (payload, template_id)
    VALUES
      (json_build_object('recipient', holder_email, 'ticket', ticket_key,
        'email', holder_email, 'title', _title, 'token', _token), 7)
    RETURNING
      id INTO _id;
    RETURN _id;
  END;
$$ LANGUAGE plpgsql;

There are a couple of instances when the function would raise an exception. I understood the documentation that this should abort the function execution. Yet, I don't believe that this is what is happening.

I see the following error: insert or update on table "ticket_transfers" violates foreign key constraint. This means that despite the exceptions, the execution reaches: INSERT INTO ticket_transfers. This should not be happening. I would expect that the below would stop the execution

IF _tickets_count <> 1 THEN
  RAISE EXCEPTION 'Error finding your ticket, contact support';
END IF;

Please advise!

CodePudding user response：

The first SELECT probably sets _tickets_count to NULL, because the query returns no row. Since NULL <> 1 is not TRUE, the error is not triggered.