Re: sequence skips 30 values, how?

On 1/31/07, Geoffrey <esoteric(at)3times25(dot)net> wrote:
> We are trying to track down an issue with our PostgreSQL application.
> We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3.
>
> We have a situation where the postgres backend process drops core and
> dies. We've tracked this to an unusual situation where a sequence value
> that is being created during the process that is causing the core file
> generation. The thing that is bizarre is that the sequence value skips
> 30+ entries.
>
> How is this even possible? Any suggestions would be greatly appreciated.
>

In addition to previous suggestions (rollback/server crashes), perhaps
a hint where you can look at. I personally had an experience like this
with sequences in Oracle. An default oracle sequence caches 20 records
in memory, which caused the unused records to be "gone" on a server
reboot, and worse, in case the sequence was "swapped" out of memory
because it wasn't used for a while.

I don't know if you used the "cache" statement while creating your
sequence. But in case you did, this can also explain why you have
"missing" sequence records. But you must have explicitly set the cache
option, since the default of postgresql is 1 [1], not 20 [2].

Please note, my findings with cache are based on the behavour of
Oracle. I didn't test this with postgresql.

Regards,
Wessel van Norel

[1] http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html
[2] http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6014.htm#sthref5342