Thread: 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.
postgresql <pgsql@symcom.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
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
ifthere'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@unc.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
>
From: "postgresql" <pgsql@symcom.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
tablelike
CREATE TABLE jobs ( job_id serial PRIMARY KEY, employee_id integer
REFERENCESstaff, ... );
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
guaranteedto 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
companymust 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
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@Yahoo.com>
To: postgresql <pgsql@symcom.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.
>
Thanks Micheal,
I guess what I am trying to figure out is, 'how important is this in
reality, in a working setup'. I know that at times I want things to be
perfect, and it is not necessary.
I was thinking about how I do this manually.
I would take a sheet of paper, write numbers down the left column.
make a couple of sheets with increasing numbers. As jobs enter the
shop, I populate the meta data. If a job gets killed, it is marked as
dead but the number does not go away. It could be used again.
What I really need to do is:
Insert "blank" into openjobs;
SELECT currval('jobs_job_id_seq');
UPDATE openjobs "job meta data" where jobno= "returned number
from prev select"
Even if this job fails, I have the empty row. I can choose to put a job
in there later if I want. Or leave it blank. I feel that all the numbers
have to be accounted for as used or null. But it should not skip.
Thanks,
Ted
-----Original Message-----
From: Michael Ansley
<Michael.Ansley@intec-telecom-systems.com>
To: 'postgresql' <pgsql@symcom.com>
Date: Fri, 23 Mar 2001 13:22:09 -0000
Subject: RE: [SQL] creating "job numbers"
> The procedure is something more like this:
>
> The first backend grabs the sequence number, say 1, and tries to
> insert.
> Whether or not this insert succeeds, the number 1 is gone from
the
> sequence.
> Then backend two tries to insert, and grabs the number 2 from the
> sequence.
> After this, the first backend rolls back, and doesn't insert. The next
> backend will get number 3. And so number 1 is lost. If the
session is
> caching sequence number, then even more numbers may be lost.
Anyway,
> the
> principle is that sequences do not roll back. Once you have a
number,
> it's
> gone, whether or not you use it. This is because keeping track of
> numbers
> to keep them contiguous is a time-consuming exercise, and
causes
> locking
> problems. So, the principle is that a sequence will always give you
a
> distinct number, but not necessarily the next number.
>
> Hope this helps...
>
>
> MikeA
"postgresql" <pgsql@symcom.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
postgresql wrote:
> 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.
Concurrency will not cause your transactions to abort. It's just if you do
BEGIN; INSERT INTO ... ROLLBACK;
that the generated sequence numbers don't get rolled back. So you might find job numbers 1, 2, 4 where 3 is
missingbecause it's transaction aborted (explicit rollback or error during processing). The serial data type
willnever fill in those gaps.
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