Error when using tables beginning with capital letters

[stevenheidel]

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:

E       psycopg2.ProgrammingError: relation "public.device" does not exist

../../.pyenv/versions/3.6.3/envs/willow/lib/python3.6/site-packages/sqlalchemy/engine/default.py:470: ProgrammingError

Further down I see this:

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) relation "public.device" does not exist
E        [SQL: "\n            SELECT\n              EXISTS(\n                SELECT 1 FROM pg_namespace\n                WHERE nspname NOT IN %(ignore_sc
hemas)s\n                LIMIT 1\n              )\n              OR\n              EXISTS(\n                SELECT 1 FROM pg_tables\n                WHER
E (schemaname || '.' || tablename)::regclass::oid NOT IN %(ignore_tables)s\n                LIMIT 1\n                -- Checking for OIDs in our snapshot
 that're missing from pg_tables\n                -- will give us a list of all preexisting tables that are now\n                -- missing. Do we care?\n
              )\n              OR\n              EXISTS(\n                SELECT 1 FROM pg_extension\n                WHERE extname NOT IN %(ignore_exten
sions)s\n                LIMIT 1\n              )\n        "] [parameters: {'ignore_schemas': ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog',
'public', 'information_schema'), 'ignore_tables': (2619, 1247, 3256, 1260, 1418, 6100, 1249, 1255, 1259, 2604, 2606, 2611, 2610, 2617, 2753, 2616, 2601,
2602, 2603, 2612, 2995, 2600, 3381, 2618, 2620, ... (120 characters truncated) ...  2328, 1417, 3118, 6000, 826, 3394, 3596, 3592, 3456, 3350, 3541, 3576
, 2224, 6104, 6106, 6102, 2613, 12413, 12403, 12393, 12398, 12408, 12418, 12423), 'ignore_extensions': ('plpgsql',)}]

../../.pyenv/versions/3.6.3/envs/willow/lib/python3.6/site-packages/sqlalchemy/engine/default.py:470: ProgrammingError

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.

[dargueta]

What version of Postgres are you on? Also please add your OS, Python version, and versions of pytest-postgresql and SQLAlchemy.

[stevenheidel]

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

[stevenheidel]

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

[dargueta]

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?

[stevenheidel]

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.

[dargueta]

Unfortunately I haven't had much time to look at this. @wesleykendall do you have any thoughts on how we can fix this?