From: Jens Eliasson <for17@mac.com>
To: Muhammad Shariq Muzaffar <shariq77@yahoo.com>
To: Tim Pushor <timp@crossthread.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
Date: Wed, 12 Mar 2003 16:25:26 +0100
Message-ID: <D9B11E78-549E-11D7-81A0-0050E4604777@mac.com> (raw)
In-Reply-To: <3E6F49AB.2030008@crossthread.com>
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@postgresql.org
>> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Muhammad Shariq
>> Muzaffar
>> Sent: Wednesday, March 12, 2003 4:21 PM
>> To: pgsql-sql@postgresql.org
>> Cc: pgsql-novice@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
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: for17@mac.com, shariq77@yahoo.com, timp@crossthread.com, pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
In-Reply-To: <D9B11E78-549E-11D7-81A0-0050E4604777@mac.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