sequence last_value - Mailing list pgsql-novice

From Duncan Adams (DNS)
Subject sequence last_value
Date
Msg-id C54EF5B83335D311BCB50000C11042B102C8C64E@vodabemail1.vodacom.co.za
Whole thread Raw
List pgsql-novice
hi

Just wondering if any one has come across this problem before?

I have a table 'location'

wireman=# \d location
                                     Table "location"
  Attribute   |    Type     |                          Modifier

--------------+-------------+-----------------------------------------------
--------------
 building_key | smallint    |
 floor        | smallint    |
 ref          | varchar(16) |
 key_location | integer     | not null default
nextval('location_key_location_seq'::text)
Index: location_pkey

now all was working well until i started to get the following error.

Warning: PostgreSQL query failed: ERROR: Cannot insert a duplicate key into
unique index location_pkey in /usr/local/apache/htdocs/WireMAN/add_main.php
on line 38

line 38 executes the following sql
insert into location (building_key, floor, ref) values (" . $buil . "," .
$floor . ",'" . $floor_ref . "')
or
insert into location (building_key, floor, ref) values (1,1,'TEST 5')


On a little investigation I found the following.

wireman=# select * from location_key_location_seq;
       sequence_name       | last_value | increment_by | max_value  |
min_value | cache_value | is_cycled | is_called
---------------------------+------------+--------------+------------+-------
----+-------------+-----------+-----------
 location_key_location_seq |          4 |            1 | 2147483647 |
1 |           1 | f         | t
(1 row)

now from what I can see the 'last_value' field should be '47' the last value
of the key_location field on location.

when I tried to update I got.

wireman=# update location_key_location_seq set last_value = 47 where
last_value = 4;
ERROR:  You can't change sequence relation location_key_location_seq

Now this is not to difficult to fix, I'll just recreate the table, but what
worry's me is how did it get like this and will it happen again? or am i
totally off the mark?

sorry don't know what version of postgres I'm running. the sys admin knocked
off hours ago :{


pgsql-novice by date:

Previous
From: Patrick Coulombe
Date:
Subject: vacuum : - error oid is invalid
Next
From: James Hall
Date:
Subject: Describe Wildcard?