Search Postgresql Archives

Re: possible race condition in trigger functions on insert operations?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Kenneth Tilton <ktilton@xxxxxxxx> wrote:

> On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer
> <akretschmer@xxxxxxxxxxxxx> wrote:
> > Kenneth Tilton <ktilton@xxxxxxxx> wrote:
> >
> >> 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;
> >
> > If i where you, i would not use such a column. What happens if you
> > insert/delete a record?
> 
> We only do logical deletes. Not sure what you mean about inserts --

inserts that change the numbering...


> that is what I am working on, and they always should get the next
> highest sequence number in a year.
> 
> >
> > I would use something like to_char(row_number() over (...),'FM000') to
> > count while select.
> 
> Unfortunately it must be within the year, not overall.

That is not a problem - over (...) with, for instance, PARTITION BY ...



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux