From: Andrew McMillan <andrew@catalyst.net.nz>
To: Tim Pushor <timp@crossthread.com>
Cc: BERG Thomas <tberg@insight-system.co.jp>
Cc: pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
Date: 15 Mar 2003 20:57:12 +1300
Message-ID: <1047715031.12645.1466.camel@kant.mcmillan.net.nz> (raw)
In-Reply-To: <3E6F49AB.2030008@crossthread.com>
References: <JLEALNIBJBBPKLKFGJNCMEHMCAAA.tberg@insight-system.co.jp>
<3E6F49AB.2030008@crossthread.com>
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/
---------------------------------------------------------------------
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: andrew@catalyst.net.nz, timp@crossthread.com, tberg@insight-system.co.jp, pgsql-novice@postgresql.org
Subject: Re: SERIAL does not ROLLBACK
In-Reply-To: <1047715031.12645.1466.camel@kant.mcmillan.net.nz>
* 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