Re: creating "job numbers"

I have been working with PG for about 2 months now. I am creating a
job tracking system for my company. I have written a front end on the
workstations (all macintoshes) that seems to be working quite well.
However, I have a problem with a concept.

In my current setup I have only one workstation that is actually
inputting new jobs. So, I took the expedient way to create the job
number. Ask PG to count the rows, add a magic number and insert
this data. This all happens in one connection. What are the odds of
two people hitting the db at the same time? In the current set up nil.
There is only one entry computer. I want to change the system to use
a job number generated by PG. I created a test table and I am
playing with inserting and the sequence function works great.
However, I am at a loss of how to pick up this next (last) job. I have
read the docs and I still am confused. I can not first ask with the
number will be, and asking for the previous oid after the fact can
also lead to the same problem. so that leaves me with, 1 ask for
that last oid from this workstation ip, or 2 since a job is inserted with
data, I could do a select of this data after the insert (not very elegant).

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.

Thanks,
Ted P.

Check out nextval() and currval(). They do exactly what you need. They're
also specific to the current backend, so you can guarantee that the same
value won't be passed to two different frontend sessions.

nextval('sequencename') -> the number that will be assigned next in
the current backend; and
currval('sequencename') -> the number that was last assigned in the
current backend (undefined if there's been
no INSERT in this session)

Hope this helps.

----------------------------------------------------------------------
Andrew J Perrin - Ph.D. Candidate, UC Berkeley, Dept. of Sociology
(Soon: Asst Professor of Sociology, U of North Carolina, Chapel Hill)
andrew_perrin(at)unc(dot)edu - http://www.unc.edu/~aperrin

On Thu, 22 Mar 2001, postgresql wrote:

> I have been working with PG for about 2 months now. I am creating a
> job tracking system for my company. I have written a front end on the
> workstations (all macintoshes) that seems to be working quite well.
> However, I have a problem with a concept.
>
> In my current setup I have only one workstation that is actually
> inputting new jobs. So, I took the expedient way to create the job
> number. Ask PG to count the rows, add a magic number and insert
> this data. This all happens in one connection. What are the odds of
> two people hitting the db at the same time? In the current set up nil.
> There is only one entry computer. I want to change the system to use
> a job number generated by PG. I created a test table and I am
> playing with inserting and the sequence function works great.
> However, I am at a loss of how to pick up this next (last) job. I have
> read the docs and I still am confused. I can not first ask with the
> number will be, and asking for the previous oid after the fact can
> also lead to the same problem. so that leaves me with, 1 ask for
> that last oid from this workstation ip, or 2 since a job is inserted with
> data, I could do a select of this data after the insert (not very elegant).
>
> 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.
>
> Thanks,
> Ted P.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

postgresql <pgsql(at)symcom(dot)com> wrote:
>I can not first ask with the number will be, and asking for the previous
>oid after the fact can also lead to the same problem.

If you use sequences and the currval() and nextval() functions, you can. See
e.g. http://www.postgresql.org/docs/aw_pgsql_book/node85.html .

HTH,
Ray
--
"a infinite number of monkeys typing into GNU emacs would never make a good
program"
.../linux/Documentation/CodingStyle

From: "postgresql" <pgsql(at)symcom(dot)com>

> In my current setup I have only one workstation that is actually
> inputting new jobs. So, I took the expedient way to create the job
> number. Ask PG to count the rows, add a magic number and insert
> this data. This all happens in one connection. What are the odds of
> two people hitting the db at the same time? In the current set up nil.
> There is only one entry computer. I want to change the system to use
> a job number generated by PG. I created a test table and I am
> playing with inserting and the sequence function works great.
> However, I am at a loss of how to pick up this next (last) job. I have
> read the docs and I still am confused. I can not first ask with the
> number will be, and asking for the previous oid after the fact can
> also lead to the same problem. so that leaves me with, 1 ask for
> that last oid from this workstation ip, or 2 since a job is inserted with
> data, I could do a select of this data after the insert (not very
elegant).

I wouldn't use oid's for this - create a jobnum field and use a sequence.

Sequences are smarter than you think, use:
select currval('mysequence') to get the current value and
select nextval('mysequence') to get the next value *for this backend*

So - each client will be guaranteed a unique number. Note that if you "use
up" a number and e.g. an insert fails there will be gaps in your numbering.

Also check out the SERIAL data-type which can provide automatic numbering
for the fields.

I'm sure there are examples in Bruce's book (there's a link on
www.postgresql.org)

- Richard Huxton

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(at)Yahoo(dot)com #

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

Jan,

Thanks, I must be missing something here. Bear with me, I am
trying to form an intelligent question.

Using the serial data type... I don't understand when the backend
would skip a number.
If the db is assigning the number with the insert, then if two (or
more) clients are trying to insert into the db at the exact same time,
only those that are successful should get a number. I am trying to
envision a situation where two clients hit at the same time and
because of problem with the insert, one aborts and the serial data
number is skipped. I would have assumed that the aborted insert is
just skipped no harm done.

I guess that I could then break the insert down into two parts. Insert
only the client name in order to grab the next job number then
update the row. I think I should be able to reduce the number of
aborted inserts to 1 in a couple of thousand were the abort is due to
client input error. (I wanted to say in a million but that just seemed
too far fetched) The only reason that I can think of that would cause
an abort would be that data was currupted in transit to the db. Or the
front end crashed and sent bad data.

Is this reasonable? I feel that people with more expierence might
shed a little light here.

Thanks for your time.

Ted

-----Original Message-----
From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: postgresql <pgsql(at)symcom(dot)com>
Date: Thu, 22 Mar 2001 14:48:19 -0500 (EST)
Subject: Re: [SQL] creating "job numbers"

>
> 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.
>

"postgresql" <pgsql(at)symcom(dot)com> writes:
> Using the serial data type... I don't understand when the backend
> would skip a number.

The value returned by a nextval() call will not be returned again by
other nextval() calls, even if the surrounding transaction is later
rolled back. Agreed, this isn't in line with full transactional
semantics, but it was deemed the more useful thing to do precisely
because of that. If you want the other behavior you can build it
yourself, whereas there's no way to build the actual behavior of
sequence objects in plain SQL.

The reason why this is more useful is that with this behavior,
acquirers of serial numbers don't need to wait for each other. A
no-skipped-numbers implementation requires each would-be acquirer to
block waiting to see if previous acquirers commit or not. You get no
concurrency at all if you build your system like that.

regards, tom lane