PostgreSQL中RAISE EXCEPTION未终止函数执行的问题求助

你遇到的核心问题是第一个异常判断逻辑未正确触发，导致函数执行到INSERT INTO ticket_transfers时触发外键约束错误。原因在于：

• 第一个查询使用GROUP BY events.name，如果没有匹配的票证记录，整个查询不会返回任何行，此时_tickets_count会被赋值为NULL。
• PL/pgSQL中，NULL <> 1的条件判断结果为NULL，会被当作false处理，因此不会进入RAISE EXCEPTION分支，函数继续执行后续代码。

1. 修复票证存在性校验逻辑 #

将原有的票证查询和计数校验拆分为更可靠的存在性检查，避免NULL值导致的判断失效：

-- 先检查票证是否存在且属于指定持有人
IF NOT EXISTS (
  SELECT 1 
  FROM tickets
  JOIN purchases ON tickets.order_id = purchases.id
  JOIN ticket_books ON tickets.book_id = ticket_books.id
  JOIN events ON ticket_books.event_id = events.id
  WHERE 
    purchases.email = holder_email 
    AND purchases.id = order_key 
    AND tickets.id = ticket_key
) THEN
  RAISE EXCEPTION 'Error finding your ticket, contact support';
END IF;

-- 单独获取活动标题
SELECT events.name INTO _title 
FROM tickets
JOIN ticket_books ON tickets.book_id = ticket_books.id
JOIN events ON ticket_books.event_id = events.id
WHERE tickets.id = ticket_key;

这种方式能确保只要票证不存在或不属于持有人，就立即抛出异常终止执行。

2. 优化转移记录计数查询 #

原查询逻辑本身无问题，但可以添加ticket_id存在性的隐式校验（通过JOIN tickets表），同时避免无效的计数查询：

SELECT COUNT(*) INTO _transfers_count
FROM ticket_transfers
JOIN tickets ON ticket_transfers.ticket_id = tickets.id
WHERE 
  ticket_transfers.ticket_id = ticket_key 
  AND ticket_transfers.status IN ('PENDING', 'ACCEPTED', 'CONFIRMED');

3. 增加并发安全保障 #

如果系统存在并发修改票证的场景，可在存在性检查时添加行锁，防止票证在校验后被删除：

IF NOT EXISTS (
  SELECT 1 
  FROM tickets
  JOIN purchases ON tickets.order_id = purchases.id
  JOIN ticket_books ON tickets.book_id = ticket_books.id
  JOIN events ON ticket_books.event_id = events.id
  WHERE 
    purchases.email = holder_email 
    AND purchases.id = order_key 
    AND tickets.id = ticket_key
  FOR UPDATE -- 锁定匹配的票证行，防止并发删除
) THEN
  RAISE EXCEPTION 'Error finding your ticket, contact support';
END IF;

4. 验证外键约束配置 #

确认ticket_transfers表的ticket_id外键确实指向tickets.id，且ticket_key参数传入的是有效的tickets.id值（可通过手动执行SELECT * FROM tickets WHERE id = <ticket_key>验证）。

内容的提问来源于stack exchange，提问作者Igor Shmukler