Re: Maybe a bug found with nextval() function - Mailing list pgsql-bugs

Look at this:

    test=> CREATE SEQUENCE x;
    CREATE SEQUENCE
    test=> \d x
       Sequence "public.x"
        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

    test=> SELECT * FROM x;
     sequence_name | last_value | increment_by |      max_value      |
    min_value | cache_value | log_cnt | is_cycled | is_called

---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------

     x             |          1 |            1 | 9223372036854775807 |
      1 |           1 |       1 | f         | f
    (1 row)

I suspect you have a very high increment_by value for the sequence:

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

I wish we had a better way to show sequence information.  \ds+ doesn't
see to show much detail.  I have added a TODO:

    * Have psql show more information about sequences

---------------------------------------------------------------------------

Alexander Troppmann wrote:
> ============================================================================
>                         POSTGRESQL BUG REPORT TEMPLATE
> ============================================================================
>
> Your name           : Alexander Troppmann
> Your email address  : talex@cocktaildreams.de
>
>
> System Configuration
> --------------------
> Architecture        : AMD Duron
> Operating System    : Fedora Core 1.A (Linux 2.4.22-1.2174.nptl)
> PostgreSQL version  : PostgreSQL-7.3.4
> Compiler used       : gcc-3.3.2
>
>
> Please enter a FULL description of your problem:
> ------------------------------------------------
>
>
> The nextval() function returns instead of the next valid integer value in the
> corresponding SEQUENCE a whole range of exactly 435 values with each function
> call... The sequence and tables have been migrated from a previous PostgreSQL
> version - newer created tables and sequences work fine!
>
>
>
>
> Please describe a way to repeat the problem.   Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>
>
> My database has been migrated from previous versions of PostgreSQL. One table
> is called "recipe" and has a column "id" of type SERIAL as primary key:
>
>  Column  | Type      |             Modifiers
> ---------+-----------+----------------------------------------------------
>  id      | integer   | not null default nextval('"recipe_id_seq"'::text)
>
> I din't use the features from a SERIAL type on this table before but at the
> moment I'm working on a complete new database frontend.... So first I tried
> to update the value of the "recipe_id_seq" SEQUENCE appropriate to the
> latest recipe.id value:
>
>    SELECT SETVAL('recipe_id_seq', max(id)) FROM recipe;
>
> returns the following output:
>
>     setval
>    --------
>        455
>    (1 row)
>
> Now I tried to fetch the next free primary key id for my "recipe" table:
>
>    SELECT NEXTVAL('recipe_id_seq') FROM receipt;
>
> I just wonder because the NEXTVAL query returns after every call exactly
> 435 rows of increasing integer values...?! Also I tested some other tables
> with a SERIAL primary key and the same result - instead of the next valid
> integer value I get a whole range of values, 435 times...
>
> A table created with a PostgreSQL 7.3.x version works fine - the SEQUENCE
> returns exactly (the next valid) integer value - so maybe the migrated table
> structures/data from my previous PostgreSQL installations cause the troubles?
>
> I already tried to fix the problem by dropping the "recipe_id_seq" and
> creating a new one - with any success... :-(
>
>
>
> best regards,
> Alex Troppmann
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073