Skip site navigation (1) Skip section navigation (2)

Re: Composite Keys

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: RPK <rohitprakash123(at)indiatimes(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Composite Keys
Date: 2007-02-26 18:44:30
Message-ID: 20070226184430.GD19104@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-general
RPK wrote:
> 
> Jorge,
> 
> For other tables I have ID field which is incremented by sequence. But for
> this table, there is not ID field. Receipt No will be incremented by finding
> the max value from the existing Receipt Nos. corresponding to that Book No.
> This case has a drawback as compared to the sequences in other tables.
> Sequences are automatically handled by the database if two users
> simultaneously enter data. But for the Receipts table where there is no need
> to define a sequence, one user will find the Max(ReceiptNo) and type in the
> rest of the entries. So there is a chance that in the meantime another user
> on a different machine will also get the same max(ReceiptNo) until the
> record of the previous user gets saved.
> 
> So how to solve this problem?

Lock the table beforehand.  Only one user can be getting the
max(ReceiptNo) that way.

Alternatively, you could use userlocks, so that you can lock, generate
the number, unlock.  And you can use it to lock that particular BookNo,
not the whole table.  (In Postgres 8.2 the facility is called "advisory
locks" and comes with the core code; in older releases it's in contrib
and it's called "userlock").

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Responses

pgsql-general by date

Next:From: Andrew HammondDate: 2007-02-26 18:49:23
Subject: Re: help required regarding queryin postgis database from google maps
Previous:From: Fabio D'OvidioDate: 2007-02-26 18:34:25
Subject: psql : password on Win32

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group