Re: Composite Keys

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.