document the behavior of the psycopg2 hstore extension

This is a weird one, and maybe there is something I don't understand about hstore or postgres, so bear with me.

Setting a python dictionary as the value of a text column should cause ProgrammingError, and it does, normally. However, if the hstore extension is enabled on the database, then no error is raised, and the dictionary is converted to an hstore representation and stored in the text column.

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 = 'postgresql://username:password@localhost:5432/your_database'
engine = create_engine(dburi)
psql_command = (
    "psql -h " + (engine.url.host or os.getenv('PGHOST')) + ' ' +
    (('-U ' + engine.url.username) if engine.url.username else '') + ' ' +
    engine.url.database
)
os.system(psql_command + " -c 'DROP EXTENSION IF EXISTS hstore'")
os.system(psql_command + " -c 'CREATE EXTENSION hstore'")  # Comment out this line to see the different behavior below
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={'foo': 'bar'})
session.add(t)

try:
    session.commit()
    # Only succeeds when the hstore extension exists
    print "Committed successfully"
except Exception as exc:
    # Always happens when the hstore extension does not exist
    print "Failed!"
    raise
finally:
    # Table only has content when the hstore extension existed
    print 'Contents of "t" table:'
    os.system(psql_command + " -c 'SELECT * FROM t;'")
    session.close()
    Base.metadata.drop_all(bind=engine)
    os.system(psql_command + " -c 'DROP EXTENSION IF EXISTS hstore'")

Just to prove that this is SQLAlchemy behavior, it does not happen using raw psycopg2:

import psycopg2

conn = psycopg2.connect(database='your_database', user='postgres', host='localhost')
curs = conn.cursor()
curs.execute('DROP EXTENSION IF EXISTS hstore')
curs.execute('CREATE EXTENSION IF NOT EXISTS hstore')
curs.execute('DROP TABLE IF EXISTS t')
curs.execute('CREATE TABLE t (id SERIAL NOT NULL, c TEXT, PRIMARY KEY (id), UNIQUE (c))')
curs.execute('COMMIT')
curs.execute('BEGIN')
curs.execute('INSERT INTO t (c) VALUES (%s) RETURNING t.id', ({},))
curs.execute('COMMIT')
curs.execute('DROP EXTENSION IF EXISTS hstore')
curs.execute('DROP TABLE t')

Versions I'm using: Postgres 9.3.2, psycopg2 2.5.2, SQLAlchemy 0.9.2.

And, yes, even though it sounds crazy, we really did have a user who was trying to set dictionary objects into string columns. :)

Comments (6)