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