pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
creating "job numbers"
9+ messages / 6 participants
[nested] [flat]

* creating "job numbers"
@ 2001-03-22 13:19  postgresql <pgsql@symcom.com>
  0 siblings, 4 replies; 9+ messages in thread

From: postgresql @ 2001-03-22 13:19 UTC (permalink / raw)
  To: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-22 13:36  Andrew Perrin <aperrin@socrates.berkeley.edu>
  parent: postgresql <pgsql@symcom.com>
  3 siblings, 0 replies; 9+ messages in thread

From: Andrew Perrin @ 2001-03-22 13:36 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-22 13:41  J.H.M. Dassen (Ray) <jdassen@cistron.nl>
  parent: postgresql <pgsql@symcom.com>
  3 siblings, 0 replies; 9+ messages in thread

From: jdassen@cistron.nl @ 2001-03-22 13:41 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-22 13:59  Richard Huxton <dev@archonet.com>
  parent: postgresql <pgsql@symcom.com>
  3 siblings, 0 replies; 9+ messages in thread

From: Richard Huxton @ 2001-03-22 13:59 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-22 19:48  Jan Wieck <JanWieck@Yahoo.com>
  parent: postgresql <pgsql@symcom.com>
  3 siblings, 1 reply; 9+ messages in thread

From: Jan Wieck @ 2001-03-22 19:48 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-23 13:16  postgresql <pgsql@symcom.com>
  parent: Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 9+ messages in thread

From: postgresql @ 2001-03-23 13:16 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: creating "job numbers"
@ 2001-03-23 15:21  postgresql <pgsql@symcom.com>
  0 siblings, 0 replies; 9+ messages in thread

From: postgresql @ 2001-03-23 15:21 UTC (permalink / raw)
  To: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-23 15:56  Tom Lane <tgl@sss.pgh.pa.us>
  parent: postgresql <pgsql@symcom.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2001-03-23 15:56 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: creating "job numbers"
@ 2001-03-23 18:09  Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jan Wieck @ 2001-03-23 18:09 UTC (permalink / raw)
  To: postgresql <pgsql@symcom.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql

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 missing because
    it's transaction aborted (explicit rollback or  error  during
    processing).  The  serial  data type will never 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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2001-03-23 18:09 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-22 13:19 creating "job numbers" postgresql <pgsql@symcom.com>
2001-03-22 13:36 ` Andrew Perrin <aperrin@socrates.berkeley.edu>
2001-03-22 13:41 ` J.H.M. Dassen (Ray) <jdassen@cistron.nl>
2001-03-22 13:59 ` Richard Huxton <dev@archonet.com>
2001-03-22 19:48 ` Jan Wieck <JanWieck@Yahoo.com>
2001-03-23 13:16   ` postgresql <pgsql@symcom.com>
2001-03-23 15:56     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-23 15:21 RE: creating "job numbers" postgresql <pgsql@symcom.com>
2001-03-23 18:09 Re: creating "job numbers" Jan Wieck <JanWieck@Yahoo.com>

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