Juan,
> I am working on a trouble ticket system in Perl, usually those
> systems
> identify a ticket by a string composed of DDMMYYYY-SERIAL, (DD = day,
> MM= month, YYYY= year, SERIAL= a serial number for the date string).
>
> I am not sure how to generate these identifiers, in case I generate
> them manually i must provide a mechanism to insure the sequence, like
> locking the table for reading/writing, get the last value, insert the
> new one and then release the lock. However i think this could be a
> big
> performance killer. Any ideas?
Yes. It is STRONGLY reccommended that you deal with this by splitting
the ID into two or more columns, so that each piece may remain atomic.
Then your formula is simple:
1. Save the first column through a to_char(DATE) system; and
2. Have the second column default off an INTEGER DEFAULT
NEXTVAL('ticket_seq') column;
3. Each day, at exactly 00:00:01 run a SETVAL('ticket_seq',1).
See? *much* easier than mucking around with user-defined types.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco