Thread: 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
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
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 > >
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 >
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
>
>
<snip>
Off the top of my head the only way I can see to reliably do this is something like the following:
BEGIN;
CREATE FUNCTION nxt_tst_id() RETURNS int4 AS '
SELECT 0 AS RESULT;
' LANGUAGE SQL VOLATILE SECURITY INVOKER;
CREATE TABLE tst (
id int4 DEFAULT nxt_tst_id(),
msg text,
PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION nxt_tst_id() RETURNS int4 AS '
LOCK TABLE tst IN SHARE MODE;
SELECT id+1 as result FROM tst ORDER BY id DESC LIMIT 1;
' LANGUAGE SQL VOLATILE SECURITY INVOKER;
INSERT INTO tst (id,msg) VALUES (0,'seed');
INSERT INTO tst (msg) VALUES ('test');
COMMIT;
However this has thwe disadvantage that the entire table must be locked in share mode to prevent further updates.
hth,
- Stuart
P.S. Sorry if this becomes HTML from the disclaimer adder thing
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.
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
On Wed, Mar 12, 2003 at 15:43:52 -0000, "Henshall, Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk> wrote: > Off the top of my head the only way I can see to reliably do this is > something like the following: Another option is to keep the number in a separate (on row) table that would be used only when doing inserts. That way you don't have conflicts with selects or updates to the main table.
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/
---------------------------------------------------------------------
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?