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.

Freebsd Partition EC2 AWS disk

In Amazon web services, we can create an AMI that takes a snapshot of our current instance. However, there are times that we need to create an instance with higher disk space using an AMI having a lower disk space.
Following the instructions from: https://www.freebsd.org/doc/handbook/disks-growing.html
After having creating the instance, and specifying a disk of 30gb, (the AMI was only 10gb) it show’s that the 20GB were free.

# gpart show ada0
=>       0  62914560  ada0  BSD  (30G)
         0        16        - free -  (8.0K)
        16  20971504     1  !0  (10G)
  20971520  41943040        - free -  (20G)

We can expand this root via the following commands:

#sudo gpart resize -i 1 -a 4k -s 29G ada0
ada0a resized

Now we can see that its ready for the partitioned,

# gpart show ada0
=>       0  62914560  ada0  BSD  (30G)
         0        16        - free -  (8.0K)
        16  60817408     1  !0  (29G)
  60817424   2097136        - free -  (1.0G)

Apply the partition using:

# sudo growfs /dev/ada0a
Device is mounted read-write; resizing will result in temporary write suspension for /.
It's strongly recommended to make a backup before growing the file system.
OK to grow filesystem on /dev/ada0a, mounted on /, from 10GB to 29GB? [Yes/No] Yes
super-block backups (for fsck_ffs -b #) at:
 21798272, 23080512, 24362752, 25644992, 26927232, 28209472, 29491712, 30773952, 32056192, 33338432, 34620672, 35902912, 37185152, 38467392, 39749632, 41031872, 42314112, 43596352, 44878592, 46160832, 47443072, 48725312, 50007552, 51289792, 52572032, 53854272, 55136512,
 56418752, 57700992, 58983232, 60265472

Check the partition after

staging% df -h
Filesystem    Size    Used   Avail Capacity  Mounted on
/dev/ada0a     28G    7.0G     19G    27%    /
devfs         1.0K    1.0K      0B   100%    /dev
fdescfs       1.0K    1.0K      0B   100%    /dev/fd

Twitch Livestreaming on Freebsd 10.2 , Lenovo Thinkpad x220, Lumina DE

Using livestreamer + VLC finally can stream LOL Champions on twitch 2015.

   
   

Lenovo X220 , FreeBSD setup

Back to Thinkpad

I'm back to using my native setup of thinkpad + freebsd. Before it was freebsd 6.0-7.0 with Thinkpad t41. Now, its freebsd 10.2 with thinkpad x220.

Only some few setbacks in installing the wireless lan in the auto install.

but should be working with the current config

/boot/loader.conf

if_iwn_load="YES"
iwn6000rw_load="YES"

/boot/rc.conf

wlans_iwn0="wlan0"
ifconfig_wlan0="WPA DHCP"

/etc/wpa_supplicant.conf
add  the following protocol as it seems its the default nowadays:

proto = WPA RSN

:) working x220 now :) next to setup lumida desktop with some fluxbox

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()

Flask restful token auth, Flask Login, Security, PyJWT

After playing around with flask login and flask security, I realized that its good for old type of login such as form user/pass. In case u plan to use modern token logins, such as using AngularJS, I advise not using those plugins and rather write it using the native plugin of httpbasicauth.

There's a good article using flask restful token, just tweak it a bit and use PYJWT for the tokens.