Thread: sequence cache is kept forever
Hi, 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. The SeqTableData entries are fairly small (~30B), but even considering that it's still a memory leak. Not an issue for common workloads, which use just a handful of sequences, but sometimes people create a lot of temporary objects, including sequences. Or what happens when a connection calls nextval() on a sequence, the sequence gets dropped, the Oid gets reused for new sequence, and then we call nextval() again? Seems like it might cause various issues with returning bogus values from stale cache. Admittedly, it doesn't seem like a critical issue - it's been like this since 2002 (a2597ef179) [1] which separated the sequence cache from relcache, to address issues with locking. [1] https://www.postgresql.org/message-id/flat/23899.1022076750%40sss.pgh.pa.us regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Tomas Vondra <tomas.vondra@enterprisedb.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.
regards, tom lane
On 11/19/21 18:50, Tom Lane wrote: > Tomas Vondra <tomas.vondra@enterprisedb.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