Reflecting table containing only oid column raises NoSuchTableError

[Ulrich Petri]

I ran into what I think is a bug while trying to reflect a legacy database that contained a table with no columns except for the Postgres oid column (don't ask why...). That table had been created with the "WITH OIDS" option.

When using reflection on a table like this a NoSuchTable exception is raised.

The reason for that lies in sqlalchemy.engine.reflection.reflecttable. The found_table flag is set to true inside the column processing loop. This loop is not executed in my case because sqlalchemy.dialects.postgresql.base.PGDialect.get_columns excludes attributes with an attnum < 1 (attnum for the oid column is -2) returning an empty list in this case.

I'm aware that this is a very unusual case. But I'd argue that at least the exception is misleading and should be changed since the table clearly exists.

Minimal example:

createdb test
psql test <<EOF
create table test () with oids;
EOF

from sqlalchemy import create_engine, MetaData
engine = create_engine("postgresql+psycopg2://localhost/test")
meta = MetaData()
meta.reflect(bind=engine)

[Mike Bayer]

how about a table option "postgresql_reflect_oids=True" and you send that to Table? Else I have to totally change the paradigm by which the base reflection works and support tables that have no columns.

[Ulrich Petri]

Hm, I assume a table option would not help in the metadata.reflect(...) case?

I'd also be fine with the answer "SQLAlchemy doesn't support reflecting tables with no columns". However if that were to be the case I think such tables should just be ignored (with a warning message of course) and not abort the reflection process as a whole (similar to how some other unsupported database features are ignored during reflection).

[Mike Bayer]

you can pass table options to metadata.reflect().

if you just want a warning, then that's a lot easier, we just need to do something in postgresql/base.py when it selects columns, to grab columns of all attnums, but pass on the ones lower than 1, and then warn if we had rows but didn't grab columns.

that's important here, that you don't actually need to reflect this table :)

[Mike Bayer]

also, workaround for now is in your metadata.reflect() just pass along a callable to "only":

metadata.reflect(only=lambda name: name is not "the_broken_table")

[Ulrich Petri]

Thanks for the workaround.

Re not needing the table: At least in the current case the table has no impact on the application (but can't be deleted due to hysterical raisins) - so ignoring it with a warning would suffice.

[Mike Bayer]

actually what I said about the warning isn't really right, the PG has_table() function will still report that the table is there leading into the failed state in reflection.

How about, if the PG get_columns() routine finds only system columns, we just return them? That's kind of a crappy approach.

it's really leaning towards tables without columns. But im in no hurry if you can just use the "only" option for now?

[Ulrich Petri]

Yes, the workaround is fine for now