Re: SERIAL does not ROLLBACK

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/
---------------------------------------------------------------------