pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <JanWieck@Yahoo.com>
To: postgresql <pgsql@symcom.com>
Cc: PgSQL-SQL <pgsql-sql@postgresql.org>
Subject: Re: creating "job numbers"
Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST)
Message-ID: <200103221948.OAA28442@jupiter.jw.home> (raw)
In-Reply-To: <200103221311.f2MDB0t56270@mail.postgresql.org>

postgresql wrote:
> How are you professionals handling this problem? I like the ability to
> insert and have the system give me the number. As I grow into more
> workstations inputting the jobs I won't have to worry about chasing
> the next  highest number.

    Two possible ways:

    1.  If you can live with gaps in the job numbers, you can use
        the serial data type. That is, you create your table like

            CREATE TABLE jobs (
                job_id      serial PRIMARY KEY,
                employee_id integer REFERENCES staff,
                ...
            );

        Now  your  application can INSERT a row not specifying an
        explicit value for the job_id like

            INSERT INTO jobs (employee_id, ...)
                VALUES (4711, ...);

        and reading the PostgreSQL assigned job_id back with

            SELECT currval('jobs_job_id_seq');

        Even  if  there  are  other  processes  doing  the   same
        concurrently,  the  assigned  job_id  is guaranteed to be
        unique and the currval()  given  back  by  your  database
        connection isn't affected by it.

    2.  If  you  cannot  live  with  gaps  in the numbers, have a
        separate table containing counters like

            CREATE TABLE app_counter (
                count_name    text PRIMARY KEY,
                count_val     integer;
            );
            INSERT INTO app_counter VALUES ('job_id', 0);

        Now it's the duty of your application to use transactions
        and do mainly the following:

            BEGIN TRANSACTION;
            UPDATE app_counter set count_val = count_val + 1
                WHERE count_name = 'job_id';

            INSERT INTO jobs
                SELECT count_val, 4711, ...
                  FROM app_counter WHERE count_name = 'job_id';

            SELECT count_val FROM app_counter
                WHERE count_name = 'job_id';

            COMMIT TRANSACTION;

    For  method  1, transaction aborts can cause missing job_id's
    because sequence numbers aren't rolled back.  OTOH  method  2
    will lock the table app_counter at the UPDATE and release the
    lock at COMMIT. So it'll have a little less  throughput  than
    method  1,  but  if you really get a performance problem with
    creating job's in the database, your company must be  gushing
    cash  and  there  should  be  plenty of money for some bigger
    boxes :-).


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




view thread (9+ messages)  latest in thread

Message-ID: <200103221948.OAA28442@jupiter.jw.home>
Permalink:  ../200103221948.OAA28442@jupiter.jw.home/
Also on:    postgresql.org/message-id/200103221948.OAA28442@jupiter.jw.home

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: JanWieck@Yahoo.com, pgsql@symcom.com
  Subject: Re: creating "job numbers"
  In-Reply-To: <200103221948.OAA28442@jupiter.jw.home>
  References: <200103221311.f2MDB0t56270@mail.postgresql.org> <200103221948.OAA28442@jupiter.jw.home>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox