Thread: sequence cache is kept forever

sequence cache is kept forever

From
Tomas Vondra
Date:
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



Re: sequence cache is kept forever

From
Tom Lane
Date:
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



Re: sequence cache is kept forever

From
Tomas Vondra
Date:

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