Re: sequence cache is kept forever

On 11/19/21 18:50, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> writes:
>> While working on a patch, I noticed that we never clean the cache of
>> sequence values, i.e. seqhashtab in sequence.c. That is, once we create
>> an entry for a sequence (by calling nextval), it will stay forever
>> (until the backend terminates). Even if the sequence gets dropped, the
>> entry stays behind.
>
> It might be reasonable to drop entries when their sequence is dropped,
> though I wonder how much that would move the needle for real-world
> usages. Dropping an entry "just because" risks losing cached value
> assignments, which might be unpleasant (e.g. due to faster advancement
> of the sequence's counter, more WAL traffic, etc). With no actual
> complaints from the field, I'm disinclined to do that.
>

My point was about dropped sequences. I certainly agree we shouldn't
discard entries for sequences that still exist.

I ran into this while working on the "decoding of sequences" patch.
Hannu Krosing proposed to track sequences modified in a transaction and
then log the state just once at commit - the seqhashtab seems like a
good match. But at commit we have to walk the hashtable to see which
sequences need this extra logging, and if we never discard anything it's
going to be more expensive over time.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company