Re: SERIAL does not ROLLBACK

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
>