Re: Workaround or user defined type

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