PostgreSQL事务能否处理并发？多请求下发票号是否重复？

我是PostgreSQL新手，已创建一个PL/pgSQL函数public.rpc_purchase_create，用于向res_purchase、res_purchase_detail两张表插入数据，并更新res_invoice_number表以获取最新发票号。该函数在Supabase中使用，Flutter单请求调用时发票号正常。现咨询：若多个客户端同时请求该函数，是否会生成重复的发票号？

函数代码 #

create or replace function public.rpc_purchase_create(json text)
    returns int
    language 'plpgsql'
    security definer
as $body$
declare json_main jsonb;
declare json_detail jsonb;
declare invoice_number integer;
declare main_id text;
declare client_code text;
declare invoice text;
begin
  --assign json
  json_main := json::jsonb;
  json_detail := (json_main -> 'list_purchase_detail_model') :: jsonb;
  --assign sale id
  main_id := (json_main ->> 'id') :: text;
  client_code := (json_main ->> 'client_id') :: text;
  invoice := (json_main ->> 'invoice') :: text;
  invoice := split_part(invoice,'/',1);
  --increase invoice number
  with x as (update res_invoice_number set number = number + 1 where code = 'PO' and client_id=client_code returning number) select x.number into invoice_number from x;
  --insert into main
  insert into res_purchase (id,client_id,supplier_id,datetime,due_date,invoice,converted,reference,note,list_picture,sub_total,discount_percent,discount_amount,
                            discount_total,total,vat,vat_total,grand_total,amount_paid,amount_left,active,create_by,create_at,modify_by,modify_at) 
  values (
    main_id,
    client_code,
    json_main->>'supplier_id',
    (json_main->>'datetime')::bigint,
    (json_main->>'due_date')::bigint,
    concat(invoice,'/' ,trim(to_char(invoice_number,'000000'))), 
    '',
    json_main->>'reference',
    json_main->>'note',
    (json_main->>'list_picture')::jsonb,
    (json_main->>'sub_total')::float4,
    (json_main->>'discount_percent')::int2,
    (json_main->>'discount_amount')::float4,
    (json_main->>'discount_total')::float4,
    (json_main->>'total')::float4,
    (json_main->>'vat')::float4,
    (json_main->>'vat_total')::float4,
    (json_main->>'grand_total')::float4,
    (json_main->>'amount_paid')::float4,
    (json_main->>'amount_left')::float4,
    (json_main->>'active')::bool,
    json_main->>'create_by',
    (json_main->>'create_at')::bigint,
    null,null
  );
  --insert into detail
   insert into res_purchase_detail (
        purchase_id,
        product_id,
        product_name,
        description,
        qty,
        cost,
        discount,
        amount
    ) 
    select main_id,
           dt.data ->> 'product_id',
           dt.data ->> 'product_name',
           dt.data ->> 'description',
           (dt.data ->> 'qty')::float4,
           (dt.data ->> 'cost')::float4,
           (dt.data ->> 'discount')::int4,
           (dt.data ->> 'amount')::float4
    from jsonb_array_elements(json_detail) as dt(data);
  return invoice_number;
end;  

$body$;

回答 #

不会生成重复发票号。

你的函数中通过WITH子句执行的UPDATE语句是原子性操作：当多个客户端同时发起请求时，PostgreSQL会对res_invoice_number表中符合code='PO'且client_id=client_code的目标行加排他锁，同一时间只有一个事务能修改该行数据，其他事务会等待锁释放后再执行。

这意味着每个请求的发票号递增操作会串行执行，每次UPDATE都会返回唯一的递增后编号，确保invoice_number不会重复。

额外提示：如果res_invoice_number表中不存在对应code='PO'和client_id=client_code的行，上述UPDATE不会生效，invoice_number会被赋值为NULL，进而导致后续插入res_purchase表时出错。建议提前确保该行存在，或者在函数中添加初始化逻辑（比如使用INSERT ... ON CONFLICT语句来创建初始编号行）。

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