Skip site navigation (1) Skip section navigation (2)

Re: Restart increment to 0 each year = re-invent the sequences mecanism ?

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: btober(at)computer(dot)org
Cc: pgsql-ml(at)baguette(dot)net, pgsql-general(at)postgresql(dot)org
Subject: Re: Restart increment to 0 each year = re-invent the sequences mecanism ?
Date: 2004-04-26 17:02:43
Message-ID: 20040426170243.GA18684@wolff.to (view raw or flat)
Thread:
Lists: pgsql-general
On Mon, Apr 26, 2004 at 09:38:41 -0400,
  btober(at)computer(dot)org wrote:
> > You don't have to mess with sequences.
> > If there are two fields ID and year then the next number is:
> >
> > next_number := ( select ID from table_name where year =
> > year_from_current_date order by ID desc limit 1 ) + 1;
> 
> Gee, I wonder why no one else thought of that... lets see, what is this
> thing I've heard about called, er, what was that word... oh yeah, I
> remember: "concurrency". Hmm, I wonder...

Yes, you need to do a lock table if you do things that way. But that
still is probably what he wants to do. Sequences can leave gaps, which
he probably doesn't want. If that is true then things need to be
serialized somehow. I doubt that these reports are being generated
at such a rate that he needs to worry about contention problems with
locking the table.

In response to

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2004-04-26 17:12:32
Subject: Re: List Removal
Previous:From: Andrew DunstanDate: 2004-04-26 17:00:35
Subject: Re: [HACKERS] TPC H data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group