nextval(seq_name) not fetching correct value from DB

Question

I have a flask with sqlalchemy tied to a postgres db. All components are working with reads fully functional. I have a simple model:

class School(db.Model):
    __tablename__ = 'schools'
    id = db.Column(db.Integer, db.Sequence('schools_id_seq'), primary_key=True)
    name = db.Column(db.String(80))
    active = db.Column(db.Boolean)
    created = db.Column(db.DateTime)
    updated = db.Column(db.DateTime)

    def __init__(self, name, active, created, updated):
        self.name = name
        self.active = active
        self.created = created
        self.updated = updated

which is working on a postgres table:

CREATE SEQUENCE schools_id_seq;

CREATE TABLE schools(
  id  int   PRIMARY KEY NOT NULL DEFAULT nextval('schools_id_seq'),
  name  varchar(80) NOT NULL,
  active boolean DEFAULT TRUE,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER SEQUENCE schools_id_seq OWNED BY schools.id;

when I work with an insert on this table from psql, all is well:

cake=# select nextval('schools_id_seq');
 nextval 
---------
      65
(1 row)

cake=# INSERT INTO schools (id, name, active, created, updated) VALUES (nextval('schools_id_seq'),'Test', True, current_timestamp, current_timestamp);
INSERT 0 1

resulting in:

66 | Test                           |          0 | t      | 2016-08-25 14:12:24.928456 | 2016-08-25 14:12:24.928456

but when I try the same insert from flask, stack trace complains about a duplicate id, but it is using nextval to get that value:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "schools_pkey" DETAIL: Key (id)=(7) already exists. [SQL: "INSERT INTO schools (id, name, active, created, updated) VALUES (nextval('schools_id_seq'), %(name)s, %(active)s, %(created)s, %(updated)s) RETURNING schools.id"] [parameters: {'active': True, 'name': 'Testomg', 'updated': datetime.datetime(2016, 8, 25, 14, 10, 5, 703471), 'created': datetime.datetime(2016, 8, 25, 14, 10, 5, 703458)}]

Why would the sqlalchemy call to nextval not return the same next val that the same call within the postgres db yields?

UPDATE: @RazerM told me about the echo=true param that I didn't know about. With

app.config['SQLALCHEMY_ECHO']=True

I yielded from a new insert (note that on this try it fetched 10, should be 67):

2016-08-25 14:47:40,127 INFO sqlalchemy.engine.base.Engine select version()
2016-08-25 14:47:40,128 INFO sqlalchemy.engine.base.Engine {}
2016-08-25 14:47:40,314 INFO sqlalchemy.engine.base.Engine select current_schema()
2016-08-25 14:47:40,315 INFO sqlalchemy.engine.base.Engine {}
2016-08-25 14:47:40,499 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2016-08-25 14:47:40,499 INFO sqlalchemy.engine.base.Engine {}
2016-08-25 14:47:40,594 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2016-08-25 14:47:40,594 INFO sqlalchemy.engine.base.Engine {}
2016-08-25 14:47:40,780 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2016-08-25 14:47:40,780 INFO sqlalchemy.engine.base.Engine {}
2016-08-25 14:47:40,969 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-25 14:47:40,971 INFO sqlalchemy.engine.base.Engine INSERT INTO schools (id, name, active, created, updated) VALUES (nextval('schools_id_seq'), %(name)s, %(active)s, %(created)s, %(updated)s) RETURNING schools.id
2016-08-25 14:47:40,971 INFO sqlalchemy.engine.base.Engine {'name': 'Testing', 'created': datetime.datetime(2016, 8, 25, 14, 47, 38, 785031), 'active': True, 'updated': datetime.datetime(2016, 8, 25, 14, 47, 38, 785050)}
2016-08-25 14:47:41,064 INFO sqlalchemy.engine.base.Engine ROLLBACK

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "schools_pkey" DETAIL: Key (id)=(10) already exists. [SQL: "INSERT INTO schools (id, name, active, created, updated) VALUES (nextval('schools_id_seq'), %(name)s, %(active)s, %(created)s, %(updated)s) RETURNING schools.id"] [parameters: {'updated': datetime.datetime(2016, 8, 25, 14, 54, 18, 262873), 'created': datetime.datetime(2016, 8, 25, 14, 54, 18, 262864), 'active': True, 'name': 'Testing'}]

Answer 1

Sequences are always incremented, so both your select statement and SQLAlchemy incremented the value.

As stated in Sequence Manipulation Functions:

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute nextval concurrently, each will safely receive a distinct sequence value.

If a sequence object has been created with default parameters, successive nextval calls will return successive values beginning with 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE command; see its command reference page for more information.

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.

Answer 2

Well, solution is simple in that case, it doesn't explain why, because I think we should look at entire environment, which you cannot show us or it will take too long. So try to insert as many records as it will reach 67 and next inserts should apply without any error, because sequence minimum will reach proper value. Of course you can try to add server_default option to id property first:

 server_default=db.Sequence('schools_id_seq').next_value()

So

seq = db.Sequence('schools_id_seq')

And in a class:

id = db.Column(db.Integer, seq, server_default=seq.next_value(), primary_key=True)

Sqlalchemy mention about that in this way:

Sequence was originally intended to be a Python-side directive first and foremost so it’s probably a good idea to specify it in this way as well.