-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Kenneth Tilton Sent: Tuesday, November 22, 2011 12:26 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: possible race condition in trigger functions on insert operations? Bit of a trigger NOOB Q: I am trying to use a trigger function to automatically populate new rows in a table with a public ID of the form YYYY-NNN such that the 42nd row created in 2011 would get the ID "2011-042". Each row is associated via an iasid column with a row in an audit table that has a timestamp column called created. This works OK, but I am worried about two rows getting the same case_no if they come in at the same time (whatever that means): declare case_yr integer; yr_case_count bigint; begin select date_part('year', created) into case_yr from audit where audit.sid = NEW.iasid; select count(*) into yr_case_count from fwa_case, audit where fwa_case.iasid=audit.sid and date_part('year', created) = case_yr; NEW.case_no = to_char( case_yr, '9999' ) || '-' || to_char(1+yr_case_count, 'FM000'); return NEW; end; Do I have to worry about this, or does ACID bail me out? If the former, what do I do? I am thinking first put a uniqueness constraint on the column and then figure out how to do retries in a trigger function. kenneth -------------------------------------------------------- Why can't you just use a sequence? Yes, gaps end up being possible but that usually is not a big issue and you will avoid any possibility of collision. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
