Postgres RAISE EXCEPTION does not abort function execution

Question

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!

Use a RAISE INFO to get the values for _tickets_count and _transfers_count, we can now only guess — Frank Heikens
– Frank Heikens, Commented Nov 1, 2022 at 20:33

Laurenz Albe · Accepted Answer · 2022-11-02 05:32:17Z

1

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.

answered Nov 2, 2022 at 5:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres RAISE EXCEPTION does not abort function execution

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related