Open stevenheidel opened 6 years ago
What version of Postgres are you on? Also please add your OS, Python version, and versions of pytest-postgresql and SQLAlchemy.
For sure, thanks for your help:
PostgreSQL 10.0 Mac OS X High Sierra Python 3.6.3 SQLAlchemy 1.1.15 pytest-pgsql 1.1.0 pytest 3.3.2
Minimum test case:
from sqlalchemy import Table, MetaData, Column, String
device_table = Table('Device', MetaData(),
Column('device_id', String, primary_key=True))
def test1(postgresql_db):
postgresql_db.create_table(device_table)
def test2(postgresql_db):
postgresql_db.create_table(device_table)
Changing 'Device' to 'device' returns no errors
Could you try using the transacted_postgresql_db
fixture instead? It's actually quite a bit faster if you're not doing anything too special. (More info on what I mean here.)
Anyway, I've found the problem. The issue is that we use SQLAlchemy to issue the DDL for creating tables in create_table()
, but we drop them manually to avoid the overhead of reflecting SQLAlchemy models back from the database to drop them.
Since SQLAlchemy doesn't quote identifiers if it doesn't need to, the DDL looks like
CREATE TABLE Device (device_id TEXT PRIMARY KEY);
Postgres lowercases unquoted identifiers behind our back so the table created is actually named device
.
In the cleanup phase, we drop the tables ourselves, so the statement issued is
DROP TABLE "Device" CASCADE;
Here the name is quoted, so Postgres looks for a table named Device
but doesn't find one.
Not sure how to go about fixing this yet. Ideas?
transacted_postgresql_db
works great, thanks!
As for postgresql_db
I'm not convinced that the problem you identified is actually what's happening. The error I get when running test2()
is this:
E psycopg2.ProgrammingError: relation "Device" already exists
[SQL: '\nCREATE TABLE "Device" (\n\tdevice_id VARCHAR NOT NULL, \n\tPRIMARY KEY (device_id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
This suggests to me that it's creating a capital "Device" but not dropping it properly rather the other way around.
Unfortunately I haven't had much time to look at this. @wesleykendall do you have any thoughts on how we can fix this?
I use
create_table
to create a table named "Device". It works fine for the setup but when pytest-pgsql goes to rollback the changes this error is thrown:Further down I see this:
This causes a problem in that the table is not rolled back, so when the next test runs and tries to create it again it fails.
Interestingly, the problem goes away when I change the name of the table to "device". Sadly, this is not something I can fix in our Postgres DB itself to rename these tables.