🚀 go-pugleaf

Hello Francesco,

Ok, if your need is to obtain contiguous ids your way is sure right.
But what if the table is used by many users and the row creation process
involves much time?
You gain a lot of waiting locks...
If you can have temporary holes in your id sequence you can use the
following query:

SELECT MIN(t1.id)+1
FROM <table_name> t1
WHERE NOT EXISTS
            SELECT t2.id
            FROM <table_name> t2
            WHERE t2.id = t1.id + 1

So if there are holes in your id sequence, the next query will fill the
first hole of the sequence.

Ciao... Umberto Uderzo

"Formentz" <francesco@leotron.com> ha scritto nel messaggio
news:3b441a9c$2_2@dnews...
> I can't see the problem with rollback the id rows, because the first
command
> I do in every query that needs a counter is to update the counters table
( I
> wrapped the insert into in a funcion that adds the right code to get the
> counter value, so if I will find a better way, I can easily change the
code
> in the function ), and so, if there are more than one queries to be
> executed, only one at a time will be executed, since the counters table is
> locked by the repeatable read transaction isolation.
> I did it so because I can't loose any numbers of the id.