Re: SERIAL does not ROLLBACK

From: "Henshall, Stuart - Design & Print" <SHenshall(at)westcountry-design-print(dot)co(dot)uk>
To: 'Tim Pushor' <timp(at)crossthread(dot)com>
Cc: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SERIAL does not ROLLBACK
Date: 2003-03-12 15:43:52
Message-ID: E382B5D8EDE1D6118DBE0008C759BCD6116BD1@WCPEXCHANGE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Manfred Koizar 2003-03-12 16:24:48 Re: SERIAL does not ROLLBACK
Previous Message Jens Eliasson 2003-03-12 15:25:26 Re: SERIAL does not ROLLBACK