Re: where's the gap in the sequence ?

On Sun, Mar 20, 2005 at 05:17:03AM +0100, Zouari Fourat wrote:

> i have a column 'id' wich is a sequence incremented by 1
> i did a verification on my table and found that i probably got some
> lines deleted from the table.
> i compared the count(*) and the max(id) and found that there is a
> difference of 400 lines.

Deletion isn't the only way to get sequence gaps -- rolled back
transactions can cause gaps since sequences themselves don't roll
back. You'll have to determine the actual cause based on your
knowledge of the database and the applications that use it.

> how to seek that gap in my table ? i want to know wich id value every
> line from the 400 deleted lines got.

One way would be to get a sorted list of ids and loop through them
looking for current_id > last_id + 1; the gaps would be the numbers
in between. You could do that in whatever client-side language you
usually use, or in a server-side language like PL/pgSQL.

Another way would be to use a set-returning function like 8.0's
generate_series() to generate all numbers obtained from the sequence
so far, and use EXCEPT to find the difference between that set and
the set of values from the table. Example:

SELECT id
FROM generate_series(1, (SELECT last_value FROM foo_id_seq)) AS s(id)
EXCEPT
SELECT id FROM foo
ORDER BY id;

The above query should show the id values (sorted) that are missing
from table foo, which uses sequence foo_id_seq. A downside of this
method is that it might be expensive if the table is large.

If you don't have generate_series() then you could easily create
it with PL/pgSQL or one of the other server-side languages.

I'm sure there are other ways, perhaps better ways than what I've
suggested.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/