Re: Bug #613: Sequence values fall back to previously chec

On Thu, 14 Mar 2002, Tom Pfau wrote:

> I don't fully understand the xlog files or WAL records but...
>
> Why isn't the writing of the WAL record based on the CACHE value of the
> sequence? If a request to nextval() can't be satisfied by the cache,
> the sequence on disk should be updated resulting in a WAL record being
> written.
>
> If two sessions are accessing the sequence, they will likely end up not
> writing sequential values as they have both taken a chunk of values by
> calling nextval() but the effect of this could be controlled by the user
> by selecting an acceptable value for CACHE.

I was thinking that too, just leave it up to the end user to decide the
level of performance gain they want. But the cool thing about writing
ahead to the WAL when compared to CACHE is that the on disk copy is
advanced ahead of the cached value so that if you have a cache value of
1 you still get time ordered sequences from multiple backends AND you're
only writing to the WAL once every 32 nextval's -- though the write
ahead should really be based on a multiple of CACHE since if your cache
value is 32 then you're not getting any benefit from the WAL savings.

> If I don't mind having
> session 1 write records 1-10 while session 2 interleaves those with
> records 11-20, I should set my cache to 10. If I want my id's to map to
> insertion time as closely as possible I should set the cache lower (or
> NOCACHE, is that an option?).

The CACHE value defaults to 1 which means no cache (each time nextval is
called it only grabs 1 value).

> I'm concerned that the discussion here has been of the opinion that
> since no records were written to the database using the value retrieved
> from the sequence that no damage has been done. We are using database
> sequences to get unique identifiers for things outside the database. If
> a sequence could ever under any circumstances reissue a value, this
> could be damaging to the integrity of our software.

Absolutely, we use sequences the same way. And the problem exhibits
itself regardless of whether data is being inserted or not, and
independantly of CACHE value. So this has to be fixed for both
scenarios.

-- Ben