postgresql/base.py:1913: SAWarning: Did not recognize type 'oid' of column 'propertyvalue'

[Sorin Ionuț Sbârnea]

It seems that I got an warning while using sqlalchemy with postgres and I do see any reason for having a warning for 'oid' columns on postgres.

/sqlalchemy/dialects/postgresql/base.py:1913: SAWarning: Did not recognize type 'oid' of column 'propertyvalue'

[Mike Bayer]

the reason is that "oid" is a Postgresql type that SQLAlchemy does not have a specific "type" object registered for, so the warning indicates that a generic "NULLTYPE" will be used instead.

How is this warning preventing your application from functioning? I'd like to close this.

[Sorin Ionuț Sbârnea]

While this is not a blocker, it is annoying and misleading. I don't want to disable log warnings just because of this invalid one. Obviously any postgres will have oids. I would change the message from warning to debug.

[Mike Bayer]

actually it is very unusual that you are reflecting a table that has a column which is explicitly "oid". The "oid" type in PG is an internal type that isn't for public use in schemas (see http://www.postgresql.org/docs/9.2/interactive/datatype-oid.html). You'd only get this warning if you're doing reflection on the system tables, is that what you're doing?

Also how is this message misleading? Would it help if it said, "will use NULLTYPE for this column instead" ? What's invalid about the warning?

Of course we can add a marker "OID" type but there's ever more PG types being added, not to mention new types can be added with extensions, that SQLAlchemy might not recognize. This warning can happen in a lot of scenarios. It means a real thing.

[Mike Bayer]

also note that the reflection process skips over the "implicit" oid columns in a table - so even if your table is created "WITH OIDS", there are no oid-specific columns reflected unless you've added some of your own.

if you have an ordinary table with no explicit OID types that is producing a warning, that would be a bug, show me the CREATE TABLE for that.

[Mike Bayer]

there seems to be some documentation suggesting that the oid type is used if one is using pg_largeobject, and it seems maybe you use oid in a parent table to refer to a large object. is that your use case? if this is common, it would warrant an actual OID type being added.

[Sorin Ionuț Sbârnea]

I am using reflexion on a normal table (which was not even created by me). The table does not have a column named oid but as far as I know all postgres table do have a system oid, which is not visible.

db = create_engine('postgresql+pg8000://xxx:yyy@localhost/jira', echo=False).connect()
conn = db.connect()
meta = MetaData()
meta.reflect(bind=db)
ledger = Table(DB_TABLE, meta, Column("id", Integer, primary_key=True), autoload=True, autoload_with=db, extend_existing=True)        

[Sorin Ionuț Sbârnea]

Mike, if further information is needed please let me know. I am only trying to identify what columns are in a table, so I would say it's a minimal usage. In fact I am not doing any ORM.

[Mike Bayer]

just wondering why this table makes use of "oid", if you can figure out if they are using pg_largeobject that would help. otherwise if they are just using oid for some incorrect reason, that's something else.

[Sorin Ionuț Sbârnea]

Here is the SQL structure dump for the table that gives this warning.

DROP TABLE IF EXISTS "public"."ledger_c10602";
CREATE TABLE "public"."ledger_c10602" (
    "id" int8 NOT NULL,
    "issueid" int8,
    "modified" int4,
    "pcode" varchar(255) COLLATE "default",
    "units" float8,
    "amount" float8,
    "paid" float8,
    "adjustment" float8,
    "ballance" float8,
    "dos" date,
    "dob" date,
    "title" varchar(255) COLLATE "default",
    "claim" varchar(255) COLLATE "default",
    "facility" varchar(255) COLLATE "default",
    "diag1" varchar COLLATE "default",
    "diag2" varchar COLLATE "default",
    "diag3" varchar COLLATE "default",
    "diag4" varchar COLLATE "default",
    "dx1" bool,
    "dx2" bool,
    "dx3" bool,
    "dx4" bool,
    "provider" varchar COLLATE "default"
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."ledger_c10602" OWNER TO "jira";

-- ----------------------------
--  Primary key structure for table ledger_c10602
-- ----------------------------
ALTER TABLE "public"."ledger_c10602" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

-- ----------------------------
--  Indexes structure for table ledger_c10602
-- ----------------------------
CREATE INDEX  "ledger_iid" ON "public"."ledger_c10602" USING btree(issueid ASC NULLS LAST);

[Mike Bayer]

and what is the name of the column it gives to this "oid" type, as I don't see any oid type in that table def? what version of postgres is this ? what is the output of:

from sqlalchemy import inspect
insp = inspect(my_engine)
print insp.get_columns('ledger_c10602')

[Mike Bayer]

• Added a new type :class:.postgresql.OID to the Postgresql dialect. While "oid" is generally a private type within PG that is not exposed in modern versions, there are some PG use cases such as large object support where these types might be exposed, as well as within some user-reported schema reflection use cases. fixes #3002

→ <<cset 42bbb7163ada>>

[Mike Bayer]

• Added a new type :class:.postgresql.OID to the Postgresql dialect. While "oid" is generally a private type within PG that is not exposed in modern versions, there are some PG use cases such as large object support where these types might be exposed, as well as within some user-reported schema reflection use cases. fixes #3002

→ <<cset 634127c3fce9>>