Koen Bok wrote:
Ok. Clear. But I really NEED uninterrupted sequences (Invoice numbers
and the Dutch tax system, yuck). How would one achieve that?
Good question!
I would not use PostgreSQL sequences at all and manage the next invoice
number in a table, ensuring access to the row is serialized.
WARNING: this is untested so you really should check it works as
expected first, but I think the principle is correct.
Firstly, set up a table containing a row for each type of number you
want to allocate.
-- Create a table to manage "sequence" numbers
create table numbers (name text not null, value integer not null);
-- Initialize the 'invoice' number
insert into numbers values ('invoice', 1);
Then, when you want to allocate a number use the following pattern:
-- *Always* work inside a transaction
begin;
-- Get the next 'invoice' number
select value from numbers where name = 'invoice' for update;
-- Add the invoice
insert into invoice (number) values (<the number you just got>);
-- Increment the 'invoice' number
update numbers set value = value + 1 where name = 'invoice';
-- Commit the whole lot (or rollback)
commit;
The critical stuff is that a) you are working in a transaction, and b)
you "select ... for update" from the numbers table to lock the 'invoice'
row.
Any other (concurrent) sessions will block at the "select ... for
update" call. By the time the row lock is released the 'invoice' number
will have been either updated (commit) or left completely alone
(rollback). There should be no holes in the numbering.
The main issue with this technique is that you are serializing access to
the 'invoice' row. That /may/ cause a bottleneck and is precisely the
reason why PostgreSQL manages sequences the way it does.
Hope I haven't got anything horribly wrong (it's late) and that it helps.
All the best.
- Matt
On Jun 1, 2006, at 1:53 AM, Matt Goodall wrote:
Koen Bok wrote:
Hello, I have two questions concerning sequences.
Whenever an INSERT fails and a ROLLBACK occurs, the sequence is
skipped by one. What is the best way to prevent this?
The basic answer is don't try to prevent it and don't worry about it
either.
PostgreSQL sequence updates (nextval, setval, ?) are atomic, and
outside of the current transaction, ensuring that multiple, concurrent
calls to nextval() will each receive a unique value.
See
<http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html>
for details.
I could do a SELECT max(id) FROM table and reset the sequence with it
by making a trigger on the table. But I don't think this will work
while sqlachemy does a SELECT nextval() before it inserts.
Yeah, don't do that. You'll probably screw the sequence up.
If you squish a hole in the sequence but PostgreSQL has already
allocated the "hole" to another session then you're likely to start
getting errors at INSERT time.
There starts my second question. As I understand it, sqlalchemy
submits a new id by first selecting one from the sequence. But what
if between these two queries the same id was given to another client?
That can't happen. See above link.
That would result in an error and therefore a ROLLBACK right? Is
there a way to do this on a database level?
PS. I use Postgres, I don't know how this works for other databases.
IIRC, MySQL does basically the same thing in reverse - you insert the
row and then retrieve the allocated id. I assume it is as safe as the
PostgreSQL sequence type.
--
__
/ \__ Matt Goodall, Pollenation Internet Ltd
\__/ \ w: http://www.pollenation.net
__/ \__/ e: [EMAIL PROTECTED]
/ \__/ \ t: +44 (0)113 2252500
\__/ \__/
/ \ Any views expressed are my own and do not necessarily
\__/ reflect the views of my employer.
-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
