flask sqlalchemy keeps Postgres transaction idle

Question

Im writting a flask api using flaskrestful,sqlalchemy, Postgres, nginx,uwsgi. Im a newbie to python.These are my configuration

database.py

from cuewords import app
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy.pool import NullPool
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String , Text , Boolean , DateTime, MetaData, Table ,Sequence
from sqlalchemy.dialects.postgresql import JSON


 Base = declarative_base()
 db_name="postgresql+psycopg2://admin:password@localhost:5434/database_name"
 from sqlalchemy.orm import sessionmaker
 engine = create_engine(db_name,poolclass=NullPool ,echo=True)
 Session = sessionmaker(autocommit=False ,bind=engine)
 connection = engine.connect()
 metadata = MetaData()

api.py

   class Webcontent(Resource):

    def post(self):
    session=Session()
    ...assign some params...
    try:
      insert_data=Websitecontent(site_url,publisher_id)
      session.add(insert_data)
      session.commit()
      Websitecontent.update_url(insert_data.id,session)
    except:
      session.rollback()
      raise
    finally:
      return "Data created "
      session.close()                
  else:  
    return "some value"

Here im first saving the just the url then saving all the content of the site using boilerpipe later .Idea is to move to queue later

model.py

 class Websitecontent(Base):

  @classmethod
  def update_url(self,id,session):
  existing_record=session.query(Websitecontent).filter_by(id=int(id)).first()
  data=Processing.processingContent(str(existing_record.url))

  #boilerpipe processing the content here
  #assigning some data to existing record in session 

  session.add(existing_record)
  session.commit()
  Websitecontent.processingWords(existing_record,session)

  @classmethod
  def processingWords(self,record,session)
    ...processing
    Websitecontent.saveKeywordMapping(session,keyword_url,record)

  @classmethod
  def saveKeywordMapping(session,keyword_url,record)
   session.commit()
   session.close()

So this code works perfectly in locally but its doesnt work in production .So when i check pag_stat_activity it show the state "idle in transaction". The app hangs then i have to restart the servers. i dont get it why session.close() does not close the pool connection why its keeping psql transaction state busy . Guys any help would be really appreciated.

well, are there any differences in configuration or versions between production and local dev? — Craig Ringer
– Craig Ringer, Commented Feb 21, 2014 at 14:41
No everything in same except the server im using in local and production — shiva
– shiva, Commented Feb 24, 2014 at 6:53
Well, something's got to be different, whether it's a package version, environment variables, config files, differences in networking setup, something. To track down exactly what, you're probably going to have to go through this step by step and compare what it does at every step. I don't think that's something Stack Overflow can really help with. — Craig Ringer
– Craig Ringer, Commented Feb 24, 2014 at 8:06

user3517924 · Accepted Answer · 2014-04-10 04:22:47Z

2

You are returning before closing the session:

  return "Data created "
  session.close()

I think returning inside finally might swallow the exception, as well.

answered Apr 10, 2014 at 4:22

user3517924

362 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

flask sqlalchemy keeps Postgres transaction idle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related