Re: sequence caches

The way I understand it is a sequence cache works by getting more than one sequence id
from the sequence at once, which saves shared memory locking. My question: If I make a
query like:
SELECT NEXTVAL('seqname'),NEXTVAL('seqname'),NEXTVAL('seqname');
... will that lock shared memory 3 times or just once?

Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> The way I understand it is a sequence cache works by getting more than one sequence id
> from the sequence at once, which saves shared memory locking. My question: If I make a
> query like:
> SELECT NEXTVAL('seqname'),NEXTVAL('seqname'),NEXTVAL('seqname');
> ... will that lock shared memory 3 times or just once?

3 times, unless you changed the default cache setting.

regards, tom lane

On Tue, 20 May 2003, Tom Lane wrote:

> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> > The way I understand it is a sequence cache works by getting more than one sequence id
> > from the sequence at once, which saves shared memory locking. My question: If I make a
> > query like:
> > SELECT NEXTVAL('seqname'),NEXTVAL('seqname'),NEXTVAL('seqname');
> > ... will that lock shared memory 3 times or just once?
>
> 3 times, unless you changed the default cache setting.
>
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.

How hard would it be to change the backend to lock shared memory only once
per query?

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?

regards, tom lane

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');

jks(at)selectacast(dot)net writes:
>> 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.

No you don't. Consider CASE expressions, SELECTs that will return an
uncertain number of rows, etc.

> Or do you do the locking down in
> the nextval method that knows nothing about the other nextvals?

Exactly.

> 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 nextval('seqname',3),nextval('seqname'),nextval('seqname');

Doesn't really seem worth the trouble to me. To take just one obvious
problem, what makes you think that those expressions will be evaluated
left-to-right? There are no guarantees of execution order in SQL.

regards, tom lane

On Tue, 2003-05-20 at 13:52, 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.

There is nothing in the spec about caching, nor does my Oracle
documentation mention any limitations (though it seems to imply sequence
alterations will not affect currently cached values). Since sequences
are based primarily on Oracles implementation, could someone confirm
what they do?

ALTER SEQUENCE in CVS Tip does not clear the sequence cache when
alterations have been made, although it certainly could.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor <rbt(at)rbt(dot)ca> writes:
> ALTER SEQUENCE in CVS Tip does not clear the sequence cache when
> alterations have been made, although it certainly could.

And you would persude other backends to clear their caches how?

regards, tom lane

On Tue, 2003-05-20 at 14:50, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)ca> writes:
> > ALTER SEQUENCE in CVS Tip does not clear the sequence cache when
> > alterations have been made, although it certainly could.
>
> And you would persude other backends to clear their caches how?

Good question -- but setval clears the local cache, so I figure I should
probably do at least that for ALTER SEQUENCE ... RESTART WITH ? .

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Joseph Shraibman <joseph(at)xtenit(dot)com> writes:
> Tom Lane wrote:
>> 3 times, unless you changed the default cache setting.

> OK, next question: How many times does it write the new sequence value to the disk?

Less than that --- there's aggregation of WAL updates for sequences.
See the code in backend/commands/sequence.c.

regards, tom lane