Re: sequence caches

On Tue, 20 May 2003, Tom Lane wrote:

> jks(at)selectacast(dot)net writes:
> > Is there a way to change the cache setting on a per-query basis? For that
> > matter how can the cache setting be changed at all? There is no ALTER
> > SEQUENCE command.
>
> Rod Taylor added one in CVS tip ... though now that I think about it,
> it's highly unlikely that it works in a reasonable fashion in the
> presence of caching. We might have to reconsider whether we should have
> it there at all. Rod, any thoughts about that? At the very least there
> needs to be a warning about the risks in the ALTER SEQUENCE man page.
>
> > How hard would it be to change the backend to lock shared memory only once
> > per query?
>
> Seems that would require predicting the future. How would you know how
> many times the sequence would get nextval'd in one query?
Because you parse the query before you take any action, so you know how
many times the nextval will be called. Or do you do the locking down in
the nextval method that knows nothing about the other nextvals? Perhaps
there could be a function call that retrieves X sequence numbers from the
sequence that overrides the cache settings so I could query like:
SELECT
prefetchseq('seqname',3),nextval('seqname'),nextval('seqname'),nextval('seqname');
...or just:
SELECT nextval('seqname',3),nextval('seqname'),nextval('seqname');