Re: [GENERAL] Two serial fields question

"Arthur M. Kang" wrote::
>
> Create a unique index on the two fields you want tied.
> Create a function that will increment number ( max(number)+1 ).
> Create a trigger on the table for inserts that will automatically set the
> number to the next appropriate value.
>
> Then, when you insert records, just do a "insert into table (year) value
> (2000)" and the number will auto-increment.  If you need specific examples,
> let me know and I'll send you a copy of what I did.  Actually, after doing
> that, I decided to remove the serial field (id) since that would more than
> likely someday be the limiting field.  With the unique index tied to two
> fields, you can always extract out the record you want.
>

Well, while sequences are session-protected, I'm not sure what happens
with you approach when two processes try to insert invoice at the same
time. I'd say the trigger fires fine, the function get max+1, but in the
same time the function from second trigger gets the same max+1 as the
first trigger/insert has not finished yet... and numbering is wrong.
Maybe I don't see something obvious, like triggers get session
protection the same way like sequences?

- Robert


Robert wrote:
>
> Hi,
>
>   what's the best way to insert a new invoice to the following table
>
> create table invoice_table (
>         id      serial;
>         number  int;
>         year    int;
> )
>
> The problem is of course that 'number' is unique only in the given year
> so I'd need to lock table and do something like
>
> insert into invoice_table (number,year) values (max(...)+1,2000)
>
> I guess this is fairly common problem, what's the best/standard PG way
> of
> doing it? Thanks for your help.
>
> - Robert