Thread: Problem with the sequence

sid tow wrote:
> HI
>
>      I have a problem locating the documentation for "sequence". I want to get the detailed information about the
columnspresent in a sequence table ie when I do  
>
> psql=# select * from foo_seq;
>  sequence_name       | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt |
is_cycled| is_called  
>
---------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
>  foo_seq                    |         11 |            1 | 9223372036854775807 |         1 |           1 |       0 | f
       | t 
> (1 row)
>
> I need to know where do i find documentation to know what the columns specify and I have already checked the man
pagesof create_sequence where I did not find much.  
> Can somebody give me ref to a link where I get such information.

 From psql:
\d foo_seq
Sequence "public.foo_seq"
     Column     |  Type
---------------+---------
  sequence_name | name
  last_value    | bigint
  increment_by  | bigint
  max_value     | bigint
  min_value     | bigint
  cache_value   | bigint
  log_cnt       | bigint
  is_cycled     | boolean
  is_called     | boolean

 From the manuals:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

For the ones not mentioned in the manual:
  last_value - is the last value given out by the sequence
  is_called  - says whether nextval() has been called in this session
               (and so whether it is safe to call currval())
  log_cnt    - don't know, googling suggests it's to do with WAL logging.

Does that help?
--
   Richard Huxton
   Archonet Ltd

I wrote a very basic entry about this at improveyourweb some weeks ago
(http://www.improveyourweb.com/?q=node/2). Hope it helps.

In the source code, sequence.c, you can read something like:

       /*
        * Decide whether we should emit a WAL log record.      If so, force up
        * the fetch count to grab SEQ_LOG_VALS more values than we actually
        * need to cache.  (These will then be usable without logging.)
        *
        * If this is the first nextval after a checkpoint, we must force a new
        * WAL record to be written anyway, else replay starting from the
        * checkpoint would fail to advance the sequence past the logged
        * values.      In this case we may as well fetch extra values.
        */
         if (log < fetch)...

log is log_cnt (or log_cnt-1 if is_called equals false)
fetch is cache_value (or cache_value-1 if is_called equals false)

If someone more can help, it would interesting for all understand this better.


On Thu, 03 Feb 2005 09:22:55 +0000, Richard Huxton <dev@archonet.com> wrote:
> sid tow wrote:
> > HI
> >
> >      I have a problem locating the documentation for "sequence". I want to get the detailed information about the
columnspresent in a sequence table ie when I do 
> >
> > psql=# select * from foo_seq;
> >  sequence_name       | last_value | increment_by |      max_value      | min_value | cache_value | log_cnt |
is_cycled| is_called 
> >
---------------------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
> >  foo_seq                    |         11 |            1 | 9223372036854775807 |         1 |           1 |       0 |
f        | t 
> > (1 row)
> >
> > I need to know where do i find documentation to know what the columns specify and I have already checked the man
pagesof create_sequence where I did not find much. 
> > Can somebody give me ref to a link where I get such information.
>
>  From psql:
> \d foo_seq
> Sequence "public.foo_seq"
>      Column     |  Type
> ---------------+---------
>   sequence_name | name
>   last_value    | bigint
>   increment_by  | bigint
>   max_value     | bigint
>   min_value     | bigint
>   cache_value   | bigint
>   log_cnt       | bigint
>   is_cycled     | boolean
>   is_called     | boolean
>
>  From the manuals:
> CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
>   [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
>   [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
>
> For the ones not mentioned in the manual:
>   last_value - is the last value given out by the sequence
>   is_called  - says whether nextval() has been called in this session
>                (and so whether it is safe to call currval())
>   log_cnt    - don't know, googling suggests it's to do with WAL logging.
>
> Does that help?
> --
>    Richard Huxton
>    Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

sid tow wrote:
> Thanks that should help but what about the rest of the columns can u
> explain what is "is_cycled" and "cache_value". Thanks in advance.

See the manuals. Section "SQL Commands", "CREATE SEQUENCE"

--
   Richard Huxton
   Archonet Ltd