Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sequence Current Value Resetting

Sequence Current Value Resetting

From
"Douglas Doolittle"
Date:

Twice a year we import class information into table class.  My first time doing this was with the spring import in August and the import failed because the Current Value was less than the Max ID in the table.  I changed the Current Value to the Max ID and was able to do the import.  After the import, the new Current Value matched the Max ID and I thought I had solved my problem.  I did not expect any problem today when I when to do the fall import.  I was unable to do the import and discovered that the Current Value was 156 less than the Max ID.  I advanced the current value to 21557, was able to import 276 rows, and the current value properly became 21832.  Any thoughts as to why this value keeps “slipping?”  Thanks.

 

Property                                    Value

-----                                           -----

Name                                        tblclass_classid_seq

OID                                           23937

Owner                                       dbdevel

ACL                                          (dbdevel=awrdRxt/dbdevel,jboss=arwd/dbdevel)

Current Value                            21832

Minimum                                   1

Maximum                                  9223372036854775807

Increment                                  1

Cache                                       1

Cycled                                      No

System sequence?                    No

Comment                     

 

-- Sequence: tblclass_classid_seq

 

-- DROP SEQUENCE tblclass_classid_seq;

 

CREATE SEQUENCE tblclass_classid_seq

  INCREMENT 1

  MINVALUE 1

  MAXVALUE 9223372036854775807

  START 21832

  CACHE 1;

ALTER TABLE tblclass_classid_seq OWNER TO dbdevel;

GRANT ALL ON TABLE tblclass_classid_seq TO dbdevel;

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE tblclass_classid_seq TO jboss;

Re: Sequence Current Value Resetting

From
John DeSoi
Date:
On Apr 4, 2006, at 2:02 PM, Douglas Doolittle wrote:

> Twice a year we import class information into table class.  My
> first time doing this was with the spring import in August and the
> import failed because the Current Value was less than the Max ID in
> the table.  I changed the Current Value to the Max ID and was able
> to do the import.  After the import, the new Current Value matched
> the Max ID and I thought I had solved my problem.  I did not expect
> any problem today when I when to do the fall import.  I was unable
> to do the import and discovered that the Current Value was 156 less
> than the Max ID.  I advanced the current value to 21557, was able
> to import 276 rows, and the current value properly became 21832.
> Any thoughts as to why this value keeps “slipping?”  Thanks.

I doubt there is anything wrong with the sequence. Do the records you
are importing already have an which could be coming from somewhere
other than the sequence you mentioned? If the imported records have a
value for this column, the sequence will not be advanced. Are you
doing inserts anywhere where the ID value is provided rather than
using the sequence value?



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: Sequence Current Value Resetting

From
John DeSoi
Date:
(be sure to copy the list so others can see your message and offer help)

What you have looks OK to me in that ClassID is not inserted and thus
should get the next sequence value on the insert.

If there is no other code or operation that inserts or updates the
table, I'm out of ideas.


On Apr 11, 2006, at 2:35 PM, Douglas Doolittle wrote:

> We are using an Access front-end.  The ClassID is the primary key for
> each record and that is being provided by the sequence.  Below is the
> query that I ran.  This is identical to what I ran in August other
> than
> the SemesterIDs were different.
>
> Fall 2005 - 96
> Fall 2006 - 114
>
> qry2659        (276 Records)
> ---
> INSERT INTO tblclass ( classinfolink, [section], stafflink,
> stafflink2,
> semesterlink, maxnum, location, startdate, enddate, block, labfee,
> notes, [hour], dayofweek, gbprogram, gbdirectory, cuts )
> SELECT tblclass.classinfolink, tblclass.section, tblclass.stafflink,
> tblclass.stafflink2, 114 AS SemLink, tblclass.maxnum,
> tblclass.location,
> tblclass.startdate, tblclass.enddate, tblclass.block, tblclass.labfee,
> tblclass.notes, tblclass.hour, tblclass.dayofweek, tblclass.gbprogram,
> tblclass.gbdirectory, tblclass.cuts
> FROM tblclass
> WHERE (((tblclass.semesterlink)=96));



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL