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

Re: SERIAL does not ROLLBACK

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Jens Eliasson <for17(at)mac(dot)com>
Cc: Muhammad Shariq Muzaffar <shariq77(at)yahoo(dot)com>,Tim Pushor <timp(at)crossthread(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: SERIAL does not ROLLBACK
Date: 2003-03-12 16:24:48
Message-ID: 8dmu6vk2qoslj9ojo2098jch7m6afvvmpc@4ax.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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

In response to

pgsql-novice by date

Next:From: Renê SalomãoDate: 2003-03-12 16:31:36
Subject: Re: [NOVICE] create function pl/pgsql c langauge
Previous:From: Henshall, Stuart - Design & PrintDate: 2003-03-12 15:43:52
Subject: Re: SERIAL does not ROLLBACK

pgsql-sql by date

Next:From: milaDate: 2003-03-12 18:24:23
Subject: LEFT JOIN and missing values
Previous:From: Jens EliassonDate: 2003-03-12 15:25:26
Subject: Re: SERIAL does not ROLLBACK

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