Thread: Workaround or user defined type

Hello people,

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?

The other option I see, is creating a user defined type that will take
care of keeping the sequence for me. I read the apropiate page at the
programmers manual, but i think i would need more information. Any
resources?

Regards,

Juan Jose
--
fortune generated signature:
Brook's Law: Adding manpower to a late software project makes it later

Juan Jose Natera Abreu <jnatera@net-uno.net> writes:
> 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?

How about

regression=# create sequence serial_seq;
CREATE
regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
   ?column?
--------------
 2001-11-06-1
(1 row)

regression=# select text(date(now())) || '-' || text(nextval('serial_seq'));
   ?column?
--------------
 2001-11-06-2
(1 row)

If you don't like this particular formatting then you could use to_char
to format the date as you wish.

Note that with this solution the serial numbers increase forever; they
don't reset to 1 at midnight.  I consider that a preferable behavior
anyway, but if you want the other, you could have a cron job reset the
sequence object once a day (see setval()).

            regards, tom lane

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

What you probably want is to use a PostgreSQL sequence
to create the serial part of the ticket number.  I
personally would store your "ticket number" as a
separate integer and date, with the integer being the
primary key of the record.  Not only will this make
searching for these records easier, but you can use
PostgreSQL's SERIAL type to do the grunt work of
creating the necessary sequences.

If you really want the trouble ticket numbers to look
like DDMMYYYY-SERIAL then simply do something like:

processdata=> SELECT to_char(now()::date, 'DDMMYYYY')
|| '-' || lpad(id::varchar, 10, '0') AS ticket_no FROM
foo;
     ticket_no
---------------------
 06112001-0000000001
 06112001-0000000002
 06112001-0000000003
(3 rows)

That gives you exactly what you need without having to
code any of it yourself.  PostgreSQL will happily make
sure that your sequence derived primary keys are
always unique and you can easily serve up ticket
numbers that look like what your clients expect.  It
will even be *fast*.  Chances are good that sequences
will be must faster than anything else you are likely
to cook up.

The downside is that failed transactions will leave
holes in your sequence, (it might go 1, 2, 5) but I
can't imagine that being a major problem with your
application.

By the way, you will notice that I used lpad to give
the sequence a fixed width.  It probably would be
better if you didn't do this.  On the other hand
9,999,999,999 is an awful lot of trouble tickets.

Jason

--- Juan Jose Natera Abreu <jnatera@net-uno.net>
wrote:
> Hello people,
>
> 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?
>
> The other option I see, is creating a user defined
> type that will take
> care of keeping the sequence for me. I read the
> apropiate page at the
> programmers manual, but i think i would need more
> information. Any
> resources?
>
> Regards,
>
> Juan Jose
> --
> fortune generated signature:
> Brook's Law: Adding manpower to a late software
> project makes it later
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com