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.

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

Postgres get First or Last day of the month

-- Last day of the month
CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1;
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;


-- First day of month
CREATE OR REPLACE FUNCTION public.fn_getfirstofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (date_trunc('MONTH', $1)::DATE);
end;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Usage:
SELECT fn_getlastofmonth('2014-08-03');
SELECT fn_getfirstofmonth('2014-08-03');

Tech proverbs #1

When dealing with database.  Dont insert historical or logs related manually in the code.  Create a trigger to insert to a table log when the main table is updated.  This is good for transaction histories involving money. #postgres

CodeIgniter Crud Generator

I really think that CodeIgniter Needs a Crud Generator like Yii/Cakephp. Developers like me is so poor on webdesign, and I intend to focus more on the backend. I make sure that the models are good/smooth and ok before I proceed to fixing the views.

I just tried, grocery crud. Spent an hour and found out it doesn't support (yet) postgres.