Обсуждение: 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