From: Manfred Koizar <mkoi-pg@aon.at>
To: Jens Eliasson <for17@mac.com>
Cc: Muhammad Shariq Muzaffar <shariq77@yahoo.com>
Cc: Tim Pushor <timp@crossthread.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
Date: Wed, 12 Mar 2003 17:24:48 +0100
Message-ID: <8dmu6vk2qoslj9ojo2098jch7m6afvvmpc@4ax.com> (raw)
In-Reply-To: <D9B11E78-549E-11D7-81A0-0050E4604777@mac.com>
References: <3E6F49AB.2030008@crossthread.com>
<D9B11E78-549E-11D7-81A0-0050E4604777@mac.com>
On Wed, 12 Mar 2003 16:25:26 +0100, Jens Eliasson <for17@mac.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mkoi-pg@aon.at, for17@mac.com, shariq77@yahoo.com, timp@crossthread.com, pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
In-Reply-To: <8dmu6vk2qoslj9ojo2098jch7m6afvvmpc@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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