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

Re: SERIAL does not ROLLBACK

From: Jens Eliasson <for17(at)mac(dot)com>
To: Muhammad Shariq Muzaffar <shariq77(at)yahoo(dot)com>,Tim Pushor <timp(at)crossthread(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SERIAL does not ROLLBACK
Date: 2003-03-12 15:25:26
Message-ID: D9B11E78-549E-11D7-81A0-0050E4604777@mac.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
I do understand the reasons for the developers to not have the serial 
counter to rollback. Using a sequence number for foreign keys in other 
tables, it has to be really important to have a reference that is 
unique over time. As Thomas, I would really consider the consequences 
before I'd change that.

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?

/Jens

Tim Pushor wrote:

> I had thought about this as well - I am developing an invoicing 
> application for my small business.
>
> There are definately applications that you want to ensure that there 
> are no 'holes' in the numbers, such as invoice numbers - technical 
> limitations notwithstanding..
>
> Tim
>
>
> BERG Thomas wrote:
>
>> I believe this is deliberate on the part of the postgresql 
>> programmers.  If
>> serial numbers could be rolled back, you'd have to block any other
>> transactions that wanted a new serial number until the first 
>> transaction had
>> committed.
>>
>> I don't know how to get around this, but I'd think pretty carefully 
>> before I
>> decided to, unless I were an a single-user or extremely few-user 
>> scenario.
>>
>> Berg
>>
>> -----Original Message-----
>> From: pgsql-novice-owner(at)postgresql(dot)org
>> [mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Muhammad Shariq
>> Muzaffar
>> Sent: Wednesday, March 12, 2003 4:21 PM
>> To: pgsql-sql(at)postgresql(dot)org
>> Cc: pgsql-novice(at)postgresql(dot)org
>> Subject: [NOVICE] SERIAL does not ROLLBACK
>>
>>
>> hi
>> i have a table named 'mytable' with a column of type
>> serial. After inserting tuples in a transaction, when
>> i rollback the transaction the serial number does not
>> come back to its original value and next time when i
>> insert the data, it start with the incremented value.
>> WHY?? how can i make it to come back to its orignial
>> value in case of rollback.
>>
>> thanx in advance...
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Everything you'll ever need on one web page
>> from News and Sport to Email and Music Charts
>> http://uk.my.yahoo.com
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>


In response to

Responses

pgsql-novice by date

Next:From: Henshall, Stuart - Design & PrintDate: 2003-03-12 15:43:52
Subject: Re: SERIAL does not ROLLBACK
Previous:From: Bruno Wolff IIIDate: 2003-03-12 15:13:35
Subject: Re: filtering out doubles with SELECT

pgsql-sql by date

Next:From: Manfred KoizarDate: 2003-03-12 16:24:48
Subject: Re: SERIAL does not ROLLBACK
Previous:From: Tim PushorDate: 2003-03-12 14:52:27
Subject: Re: SERIAL does not ROLLBACK

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