Re: SERIAL does not ROLLBACK

On Wed, 12 Mar 2003 16:25:26 +0100, Jens Eliasson <for17(at)mac(dot)com>
wrote:
>I have an idea that may solve your needs. Why not have another column
>for "invoice number" or what you want and make a function that iterates
>it with one based on the existing max of the column that you then use
>inside the transaction where you make the insert?
>
>Tim Pushor wrote:
>> There are definately applications that you want to ensure that there
>> are no 'holes' in the numbers, such as invoice numbers - technical
>> limitations notwithstanding..

There are no technical limitations involved here, only logical
limitations. You have to accept either (a) holes in your sequence of
numbers or (b) non-increasing numbers (i.e. later invoices having
lower numbers) or (c) serialization of the invoice creation process.

If you find a fourth way, please let me know ;-)

With Postgres sequences you get (a). Jens, your suggestion boils down
to (c), because concurrent transactions see the same max, so all but
one inserts will fail.

In other words, you can have
(.) a sequence without holes,
(.) a monotonically increasing sequence,
(.) more than one transaction creating invoices at the
same time,
choose two!

Servus
Manfred