批量插入PostgreSQL JSONB数据时触发器存储过程执行异常排查

问题场景 #

作为PostgreSQL存储过程新手，我创建了配合触发器的存储过程：

• 前置触发器调用new_resource()，负责生成/赋值ID、设置版本号并同步JSONB字段中的id与元数据
• 后置触发器调用search_appointment()，将JSONB中搜索字段提取到internal.appointmentsearch表优化查询

单条/两条插入时逻辑正常，但插入三条及以上数据时，抛出错误：

"Failed to run sql query: null value in column "id" of relation "appointmentsearch" violates not-null constraint"

猜测是批量插入时从JSONB获取ID存在时序问题，但不确定具体原因，需要分析及解决建议。

相关表结构与存储过程代码如下：

业务表与历史表结构 #

create table if not exists public.appointment (
    id text primary key,
    versionid int not null,
    updatedat timestamp with time zone default timezone('utc'::text, now()) not null,
    resource jsonb not null
);

create table if not exists public.appointmenthistory (
    id text,
    versionid int not null,
    updatedat timestamp with time zone default timezone('utc'::text, now()) not null,
    resource jsonb not null,
    primary key (id, versionid)
);

触发器定义 #

create trigger new_appointment
  before insert on public.appointment
  for each row execute procedure public.new_resource(); 
create trigger newsearch_appointment
  after insert on public.appointment
  for each row execute procedure internal.search_appointment(); 

new_resource()存储过程 #

create or replace function new_resource()
  returns trigger as $$
  declare  
  resourceid text;
  begin
    -- 检查上传的资源是否包含id
    resourceid := new.resource->>'id';
    -- 如果resourceid为空，说明是新资源，生成UUID作为ID
    if resourceid is null then
      resourceid := gen_random_uuid();
    end if;
    -- 为记录赋值ID
    new.id := resourceid;
    -- 设置初始版本号为1
    new.versionid = 1;
    -- 同步JSONB中的id与元数据
    new.resource := new.resource::jsonb || 
      json_build_object(
        'id',resourceid::text,
        'meta', json_build_object(
          'versionId','1',
          'lastuUdated',to_json(now())::jsonb
        )::jsonb
      )::jsonb;
    return new;
  end;
$$ language plpgsql security definer;

搜索表结构 #

create table if not exists internal.appointmentsearch (
  id text primary key,
  "appointment-type" jsonb,
  "date" jsonb,
  "location" jsonb,
  "patient" jsonb
);

search_appointment()存储过程 #

create or replace function internal.search_appointment()
  returns trigger as $$
  begin
    insert into internal.appointmentsearch
    (
      id,
      "appointment-type",
      "date",
      "location",
      "patient"
    )
    values
    (
      jsonb_path_query(new.resource, '$.id')::text,
      jsonb_path_query(new.resource, '$.appointmentType'),
      jsonb_path_query(new.resource, '$.start'),
      jsonb_path_query(new.resource, '$.participant.actor[*] ? (@.type like_regex "^.*Location.*") ? (@.reference like_regex "^.*Location.*")'),
      jsonb_path_query(new.resource, '$.participant.actor[*] ? (@.type like_regex "^.*Patient.*") ? (@.reference like_regex "^.*Patient.*")')
    );
  return new;
  end;
$$ language plpgsql security definer;

问题分析 #

1. ID获取方式不可靠：search_appointment()中使用jsonb_path_query(new.resource, '$.id')::text获取ID，该函数返回集合类型，批量插入时PostgreSQL对集合类型的隐式转换可能异常，导致返回null。
2. 冗余JSON查询：前置触发器new_resource()已经确保new.id与new.resource->>'id'完全一致，直接使用new.id更可靠，还能避免JSON路径查询的性能开销。
3. 路径查询适配错误：jsonb_path_query用于提取单个值时不合适，批量场景下可能因匹配结果的处理逻辑导致空值，应使用jsonb_path_query_first确保返回单个结果。

解决建议 #

1. 修改search_appointment()存储过程 #

核心修改：

• 直接使用new.id作为appointmentsearch的ID值
• 将jsonb_path_query替换为jsonb_path_query_first提取单个JSON字段
• 简单字段使用->操作符代替路径查询，提升性能

修改后代码：

create or replace function internal.search_appointment()
  returns trigger as $$
  begin
    insert into internal.appointmentsearch
    (
      id,
      "appointment-type",
      "date",
      "location",
      "patient"
    )
    values
    (
      new.id, -- 直接使用前置触发器赋值的id字段
      new.resource->'appointmentType',
      new.resource->'start',
      jsonb_path_query_first(new.resource, '$.participant.actor[*] ? (@.type like_regex "^.*Location.*") ? (@.reference like_regex "^.*Location.*")'),
      jsonb_path_query_first(new.resource, '$.participant.actor[*] ? (@.type like_regex "^.*Patient.*") ? (@.reference like_regex "^.*Patient.*")')
    );
  return new;
  end;
$$ language plpgsql security definer;

2. 修复new_resource()拼写错误 #

将lastuUdated修正为lastUpdated，确保元数据字段名正确：

new.resource := new.resource::jsonb || 
  json_build_object(
    'id',resourceid::text,
    'meta', json_build_object(
      'versionId','1',
      'lastUpdated',to_json(now())::jsonb -- 修正拼写错误
    )::jsonb
  )::jsonb;

3. 额外优化建议 #

• 若批量插入数据量极大，可将FOR EACH ROW触发器改为FOR EACH STATEMENT，配合批量插入逻辑提升性能
• 为internal.appointmentsearch的查询字段添加GIN索引，进一步优化查询速度：

  create index idx_appointmentsearch_patient on internal.appointmentsearch using gin ("patient");
  create index idx_appointmentsearch_date on internal.appointmentsearch using gin ("date");
  

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