=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D

Your name : Alexander Troppmann
Your email address : talex (AT) cocktaildreams (DOT) 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 t=
he
corresponding SEQUENCE a whole range of exactly 435 values with each functi=
on
call... The sequence and tables have been migrated from a previous PostgreS=
QL
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 tab=
le
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 trouble=
s?

I already tried to fix the problem by dropping the "recipe_id_seq" and=20
creating a new one - with any success... :-(



best regards,
Alex Troppmann



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Thu, Feb 19, 2004 at 07:16:42PM +0100, Alexander Troppmann wrote:

Ok.

You're going to get a value for every row in receipt, which is what you're
seeing.

What you want is
SELECT NEXTVAL( 'recipe_id_seq');

--Joe

--
Joe Sunday <sunday (AT) csh (DOT) rit.edu> http://www.csh.rit.edu/~sunday/
Computer Science House, Rochester Inst. Of Technology

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend