pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSERIAL does not ROLLBACK
7+ messages / 7 participants
[nested] [flat]
* SERIAL does not ROLLBACK
@ 2003-03-12 07:21 Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` Re: SERIAL does not ROLLBACK BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 07:34 ` Re: [SQL] SERIAL does not ROLLBACK Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 7+ messages in thread
From: Muhammad Shariq Muzaffar @ 2003-03-12 07:21 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
@ 2003-03-12 07:31 ` BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 14:52 ` Re: SERIAL does not ROLLBACK Tim Pushor <timp@crossthread.com>
1 sibling, 1 reply; 7+ messages in thread
From: BERG Thomas @ 2003-03-12 07:31 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` Re: SERIAL does not ROLLBACK BERG Thomas <tberg@insight-system.co.jp>
@ 2003-03-12 14:52 ` Tim Pushor <timp@crossthread.com>
2003-03-12 15:25 ` Re: SERIAL does not ROLLBACK Jens Eliasson <for17@mac.com>
2003-03-15 07:57 ` Re: SERIAL does not ROLLBACK Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 2 replies; 7+ messages in thread
From: Tim Pushor @ 2003-03-12 14:52 UTC (permalink / raw)
To: BERG Thomas <tberg@insight-system.co.jp>; +Cc: pgsql-novice@postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` Re: SERIAL does not ROLLBACK BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 14:52 ` Re: SERIAL does not ROLLBACK Tim Pushor <timp@crossthread.com>
@ 2003-03-12 15:25 ` Jens Eliasson <for17@mac.com>
2003-03-12 16:24 ` Re: SERIAL does not ROLLBACK Manfred Koizar <mkoi-pg@aon.at>
1 sibling, 1 reply; 7+ messages in thread
From: Jens Eliasson @ 2003-03-12 15:25 UTC (permalink / raw)
To: Muhammad Shariq Muzaffar <shariq77@yahoo.com>; Tim Pushor <timp@crossthread.com>; +Cc: pgsql-novice@postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` Re: SERIAL does not ROLLBACK BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 14:52 ` Re: SERIAL does not ROLLBACK Tim Pushor <timp@crossthread.com>
2003-03-12 15:25 ` Re: SERIAL does not ROLLBACK Jens Eliasson <for17@mac.com>
@ 2003-03-12 16:24 ` Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 7+ messages in thread
From: Manfred Koizar @ 2003-03-12 16:24 UTC (permalink / raw)
To: Jens Eliasson <for17@mac.com>; +Cc: Muhammad Shariq Muzaffar <shariq77@yahoo.com>; Tim Pushor <timp@crossthread.com>; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` Re: SERIAL does not ROLLBACK BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 14:52 ` Re: SERIAL does not ROLLBACK Tim Pushor <timp@crossthread.com>
@ 2003-03-15 07:57 ` Andrew McMillan <andrew@catalyst.net.nz>
1 sibling, 0 replies; 7+ messages in thread
From: Andrew McMillan @ 2003-03-15 07:57 UTC (permalink / raw)
To: Tim Pushor <timp@crossthread.com>; +Cc: BERG Thomas <tberg@insight-system.co.jp>; pgsql-novice@postgresql.org
On Thu, 2003-03-13 at 03:52, 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..
I've developed accounting systems in the past, and I've always found it
a lot easier to explain to people that number X is an ever-increasing
sequence, rather than one with no gaps. If they are told that gaps
happen, they get over it quite quickly - there are other audit trails
after all.
Trying to have a sequence with no gaps always seems to inherently have
race conditions.
That said, something like:
BEGIN;
SET TRANSACTION ISOLATION-LEVEL SERIALIZABLE;
INSERT INTO invoice (invoice_no, my, other, invoice, columns ) SELECT
invoice_no + 1, my, other, invoice, values FROM invoice ORDER BY
invoice_no DESC
COMMIT;
should do what you need, as long as you cope for the situations where
the COMMIT fails because the update couldn't be serialised.
The SEQUENCE is not rolled back because of:
Txn 1: Txn 2:
BEGIN;
SELECT NEXTVAL('seq');
INSERT some row; BEGIN;
... SELECT NEXTVAL('seq');
ROLLBACK; INSERT some row;
...
COMMIT;
What should the sequence value be for Txn 2? How would a rolled back
sequence work? It gets even trickier if the ROLLBACK of Txn1 happens
after the COMMIT of Txn 2 has succeeded...
Cheers,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SERIAL does not ROLLBACK
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
@ 2003-03-12 07:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2003-03-12 07:34 UTC (permalink / raw)
To: Muhammad Shariq Muzaffar <shariq77@yahoo.com>; +Cc: pgsql-sql; pgsql-novice@postgresql.org
On Wed, 12 Mar 2003, [iso-8859-1] Muhammad Shariq Muzaffar wrote:
> 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.
That's pretty much the point, so you can't easily. Serials (despite their
name) are really intended to give unique values not sequential ones.
Rolling back the value causes some issues with concurrency, and I think ou
can already pretty much implement a rollback one yourself with functions
and locks.
Specifically, if you have two concurrent transactions that want to get a
value from the serial, what happens? If the second transaction waits for
the first, you have poor concurrency. If it doesn't, then what happens
if the first gets say 1 and the second 2 and then the first rolls back?
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-03-15 07:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 07:21 SERIAL does not ROLLBACK Muhammad Shariq Muzaffar <shariq77@yahoo.com>
2003-03-12 07:31 ` BERG Thomas <tberg@insight-system.co.jp>
2003-03-12 14:52 ` Tim Pushor <timp@crossthread.com>
2003-03-12 15:25 ` Jens Eliasson <for17@mac.com>
2003-03-12 16:24 ` Manfred Koizar <mkoi-pg@aon.at>
2003-03-15 07:57 ` Andrew McMillan <andrew@catalyst.net.nz>
2003-03-12 07:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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