Postgres REPEATABLE READ with Foreign key error

Recently I bumped into the following error:

(psycopg2.extensions.TransactionRollbackError) could not serialize access due to concurrent update
 CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

​

I was using flask sqlalchemy in python 3.5, RDS 9.5.2. After adding a repeatable read to one of the views.  The error happened rarely and it was hard to trace. I digged into the server logs in RDS and found out there was no other transactions updating the same row.

After consulting the IRC channel and some said it was a FOREIGN key error. Indeed it was, Luckily i had an audit trigger in place and saw that the row I was updating had a foreign key to the users table. This user was active that time and did a transaction at the same moment this row is being updated.

Postgres INHERITS partition table with Flask SQLAlchemy

Sqlachemy

Sqlalchemy version 1.0.0 released a feature to support postgres inheritance. Had a quite a time to implement in a Flask-Sqlalchemy.

 1 from app import db, app, bcrypt, redis
 2 
 3 class GarrisonProperty(db.Model):
 4     __tablename__ = 'garrison_properties'
 5 
 6     id = db.Column(db.Integer(), primary_key=True)
 7     building_id =  db.Column(db.Integer())
 8     level = db.Column(db.Integer())
 9     build_cost = db.Column(db.Integer())
10     build_time = db.Column(db.Integer())
11 
12 class Townhall(db.Model):
13     __tablename__ = "townhall"
14     __table_args__ = (
15     ¦   db.PrimaryKeyConstraint('building_id', 'level'),
16     ¦   {'postgresql_inherits' : 'garrison_properties'}
17     )
18 
19     id = db.Column(db.Integer())
20     building_id =  db.Column(db.Integer())
21     level = db.Column(db.Integer())
22     build_cost = db.Column(db.Integer())
23     build_time = db.Column(db.Integer())
24     unlocks = db.Column(db.Integer())

We wanted something that is simple and doesn't really want to the SQLAlchemy join. By this implementation, it should simplify our database structure.

We had to bring all the attributes from the parent model (GarrisonProperty) into the child just to have the access. The db shouldn't do any duplicates for it.

Source:
https://bitbucket.org/zzzeek/sqlalchemy/issue/2051/support-postgresql-table-options

SQLAlchemy CSV import with postgres

Here's a way to import csv data to database using Flask, Postgres and Sqlalchemy.


@manager.command
def import_data():
    file = open("filename.txt")
    process_file('table_name', file)
    file.closed

def process_file(table_name, file_object):
    sql_statement = """
    ¦   COPY %s FROM STDIN WITH
    ¦   CSV
    ¦   HEADER
    ¦   DELIMITER AS ','
    ¦   """
    engine = db.engine
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.copy_expert(sql=sql_statement % table_name, file=file_object)
    conn.commit()