This script demonstrates a problem with PostgreSQL set-up on http://circleci.com's test machines.

circle.py

#!/usr/bin/env python
'''
The only dependencies for this script are SQLAlchemy 0.9.2, psycopg2
2.5.2, and Postgres 9.3.2, so you should be able to use it for debugging
yourselves. Also, those are the exact same versions that are being used
in all our other environments, so this problem isn't caused by a slight
version mismatch anywhere.

This test script connects to the Postgres DB, creates a table with a
text column, and then tries to write a Python dictionary object to the
text column. This should fail, and it does fail, locally, under Mac OS X,
and on Travis-CI test runners, which are running Linux. It fails when
the code tries to convert the dictionary object to a string for a SQL
INSERT statement. I'm not sure if the failure originates inside psycopg2
or in Postgres itself.

However, on CircleCI's test Postgres, the code runs all the way through,
and at the end of the test, the database table has one entry with the
empty string in the column where it tried to insert the Python diction-
ary object. Somehow, on CircleCI, the dictionary object is converted to
an empty string and allowed to be inserted. (This might sound like reason-
able behavior for MySQL but it's very unusual behavior for Postgres.)

We also have a tool that creates a temporary Postgres cluster in a temp
directory, and starts a Postgres server using that directory. When I use
this tool to create and launch a temporary Postgres cluster on a CircleCI
test instance, this test script fails in the expected way, with an error
trying to write a dictionary object into a text column.

So the *only* environment where this strange behavior occurs is on the
pre-installed Postgres instance on CircleCI. That indicates to me that
it must be some slightly different configuration.

Here's roughly how we create and launch a temporary cluster:

initdb -D $TMPDIR/db -E UNICODE
pg_ctl -o "-c unix_socket_direcotries='$TMPDIR/socket' -c listen_addresses='' -c fsync=off"  -D $TMPDIR/db -l $TMPDIR/log start
PGHOST=$TMPDIR/socket createdb -E UNICODE $DBNAME

As you can see, we have fsync off as well, so you are right, that cannot 
be the problem.

The database URI then goes in the GC_DATABASE_URI environment variable, 
like this:

GC_DATABASE_URI=postgresql+psycopg2:///$DBNAME?host=$TMPDIR/socket
'''
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import create_session
from sqlalchemy import Column
from sqlalchemy import Text, Integer
from sqlalchemy.ext.declarative import declarative_base

dburi = os.getenv('GC_DATABASE_URI', 'postgresql://dbuser:dbhost@localhost:5432/dbname')
engine = create_engine(dburi)
session = create_session(bind=engine)
Base = declarative_base()

class T(Base):
    __tablename__ = 't'
    id = Column(Integer, primary_key=True)
    c = Column(Text, unique=True)

Base.metadata.create_all(bind=engine)

session.begin()
t = T(c={})
session.add(t)
try:
    session.commit()
    print "Committed successfully"  # Only happens on CircleCI's PostgreSQL
except Exception as exc:
    # Always happens in development, Mac OS X, Linux, Travis-CI's test runners, and production deployments
    # Also happens on CircleCI when using a newly created PostgreSQL cluster
    print "Failed!"
    raise
finally:
    print 'Contents of "t" table:'  # Table only has content on CircleCI's PostgreSQL
    os.system(
        "psql -h " + (engine.url.host or os.getenv('PGHOST')) + ' ' +
        (('-U ' + engine.url.username) if engine.url.username else '') + ' ' +
        engine.url.database + ' ' +
        "-c 'SELECT * FROM t;'"
    )
    Base.metadata.drop_all(bind=engine)