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

Re: Restart increment to each year = re-invent the

From: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>
To: "Bruno Baguette" <pgsql-ml(at)baguette(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Restart increment to each year = re-invent the
Date: 2004-04-26 09:52:31
Message-ID: 1510.192.168.0.64.1082973151.squirrel@mercury.wardbrook.com (view raw or flat)
Thread:
Lists: pgsql-general
Bruno Baguette said:
> I have to design a table wich will store some action reports. Each
> report have an ID like this 1/2004, 2/2004, ... and each years, they
> restart to 1 (1/2004, 1/2005, 1/2006,...).
>
> So, I was thinking to split that in two fields : one with the increment
> and one with the year. But I don't know how can I manage the sequences
> since I have to restart to  each year...
>
> Do you think I should re-invent the sequences mecanism with a second
> table and a stored procedure, only to manage theses 'home-made' sequences
> ?
>
> Or should I create some sequences like myseq_2004, myseq_2004,
> my_seq_2005,... and use a concatenation of the myseq_ string and the
> current year when calling nextval and curvall ?

Of the options available, I would use the sequence solution; one sequence
object for each year.

You can do something along the following lines in a stored procedure
(which could be used to create the report records):

...
-- pass in the year value to the procedure
-- or determine year from year part of current date
yearNumberTxt = '2004';
-- get the next ID for the year
select nextval(yearNumberTxt) into seqNum;
-- and generate your report number
reportID := seqNum || '/' yearNumberTxt;
-- insert into your ReportTable using new report ID
...


If you use an extra table and manage the incrementing field yourself (your
other suggestion), then you need to be aware of concurrency issues when
accessing and updating the counter (for the year). You need to lock the
row in a function which generates the next number for the year, and this
will block any other processing wanting a number at the same time. Of
course if you don't create reports frequently, or concurrently then this
isn't an issue.

The sequence solution will not block, but could leave you with gaps in
your numbering if a record fails to insert for some reason after you issue
the nextval function on the sequence object.

John Sidney-Woollett

In response to

Responses

pgsql-general by date

Next:From: Stijn VanroyeDate: 2004-04-26 09:52:56
Subject: Re: What is wrong here?
Previous:From: Juris KruminsDate: 2004-04-26 09:35:31
Subject: Re: cache lookup of relation 165058647 failed

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