Question 324962 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql trigger problem with unique key constraint

I have a requirement of order no which need to start with country code + some running number which is incermenting

I did a trigger in following way. The order_no has a unique key constraint.

Now the issue I am having is that when there is concurrent order coming in. First order will be inserted and second order will be failed due to unique key constraint as 2 same order no generated.

What is the best way I can do this in database level?

CREATE OR REPLACE FUNCTION func_gen_order_no()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
DECLARE
    current_no INT;
BEGIN
    SELECT COUNT(*) INTO current_no FROM order_main dt WHERE dt.order_country_code = NEW."order_country_code"; 
    NEW."order_no" = CONCAT(NEW."order_country_code", TO_CHAR(current_no + 1, '000000FM') );

    RETURN NEW;
END;
$$;

CREATE TRIGGER tgr_gen_order_no
    BEFORE INSERT
    ON order_main
    FOR EACH ROW
    EXECUTE PROCEDURE func_gen_order_no();
Tags:

2

Answers


  1. 0

    Just don’t.

    Persisted gapless, ordinal numbers for orders is a wet dream causing nothing but pain in reality. Very awkward for concurrent write access, and will cause problems with updates and deletes sooner or later.

    Also keep country code and serial number in separate columns – each with appropriate data type, and the country code with a FOREIGN KEY constraint to enforce valid states. For display, you can always (very cheaply) concatenate. If you must, add a generated column. See:

    Use an IDENTITY column for the serial number. And be prepared to see gaps in the numbering after failed inserts / deletes / upodates. See:

    If you must have gapless numbers per country, create them after the fact in a view. See:

    Login or Signup to reply.
  2. 0

    Adjusting the answer in a similar thread to your structure and the fact you want the country_code in the order_no: you can employ generated seed-based sequences. Demo:

    create function seeded_sequence_nextval(seed text, 
                                        owner_table regclass default null,
                                        owner_column text default 'ctid') 
returns int as $f$
declare sequence_name text:=concat_ws('__','seeded_sequence',
                                           owner_table,
                                           owner_column,
                                           seed);
begin execute format('create sequence if not exists %I owned by %s;',
                     sequence_name,
                     case when owner_table is null then 'none'
                          else format('%s.%I',owner_table,owner_column)
                     end);
      return nextval(format('%I',sequence_name));
end $f$ language plpgsql;

create function f_order_numbers()returns trigger language plpgsql as $f$
begin
    select concat(coalesce(NEW."order_country_code",'__'), 
                  to_char(seeded_sequence_nextval(NEW."order_country_code",
                                                  TG_TABLE_SCHEMA::text||'.'
                                                  ||TG_TABLE_NAME::text,
                                                  'order_no'),
                          '000000FM'))
    into new.order_no;
    return new;
end $f$;

create trigger t_order_numbers before insert on order_main 
for each row execute function f_order_numbers();

    insert into order_main values 
  ('order2','CA')
 ,('order3','US')
 ,('order4',null)
 ,('order5',null)
 ,('order6','US')
  returning *;
    description order_country_code order_no
    order2 CA CA000002
    order3 US US000001
    order4 null __000001
    order5 null __000002
    order6 US US000002

    It doesn’t mind if multiple concurrent transactions target the same country code and only locks briefly the first time a given country is entered into the table. The demo also shows how to initially set up sequences for pre-existing country codes and set order_no for pre-existing entries. Sequence ownership is handy.

    If you don’t mind gaps in your sequence,

    because delete, update and rolled-back insert will cause them at some point anyways, use generated columns: one identity, one regular: demo

    alter table order_main 
  add column common_sequential_id int generated always as identity,
  add column order_no text generated always as 
    (order_country_code::text||lpad(common_sequential_id::text,6,'0')) stored;

insert into order_main values 
  ('order2','CA'),
  ('order3','US')
returning *;
    description order_country_code common_sequential_id order_no
    order2 CA 2 CA000002
    order3 US 3 US000003

    As opposed to concat() and to_char(), || and lpad() are immutable and can be used in that context. There are slight differences in their behaviour.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search