How can I make a rollback in a sequence?

[Rafael Henrique da Silva Correia]

Hi !

I have a block of code similar to this I made to test:

def TEST():

    teste = Test(descricao=str('wololo'))

    try:

        db.session.add(wololo)

        db.session.commit()

        db.session.close()

    except IntegrityError, e:

        db.session.rollback()

        db.session.close()

        print e.message

    sql = db.session.execute('select * from public.test_id_seq;')

    result = sql.fetchall()

    print "Sequence select:"

    print result[0][0]

    print result[0][1]

TEST()

My model is:

class Test(db.Model):
  id = db.Column(db.BigInteger, db.Sequence('test_id_seq', metadata=db.metadata), primary_key = True)
  description = db.Column(db.String(50), unique=True, nullable=False)
  def __init__(self, description):
    self.description = description

I dont receive any error BUUUTT my sequence increases even in a case except. My database is PostgreeSQL 9.3.5 and configuration is:

SQLALCHEMY_DATABASE_URI = 'postgresql://' + database_username + ':' + \
 database_password + "@" + database_address +":" + database_port + "/" + \
 database_name

I created the base of my project following the official documentation Flask Flask-SQLAlchemy on http://flask.pocoo.org/docs/patterns/sqlalchemy/

I read many many many examples on google, but none helped me ... already tried many things ... can anyone help me? 

Thank you!

[Wichert Akkerman]

On 20 Aug 2014, at 20:02, Rafael Henrique da Silva Correia <rafael...@gmail.com> wrote:

Hi !

I have a block of code similar to this I made to test:

def TEST():

    teste = Test(descricao=str('wololo'))

    try:

        db.session.add(wololo)

        db.session.commit()

        db.session.close()

    except IntegrityError, e:

        db.session.rollback()

        db.session.close()

        print e.message

[..]

I dont receive any error BUUUTT my sequence increases even in a case except

That’s just how PostgreSQL works. From the PostgreSQL documentation:

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, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

Wichert.

[Rafael Henrique da Silva Correia]

I did not know that .... thanks!

For future reference: http://www.postgresql.org/docs/9.1/static/functions-sequence.html