1. Michael Bayer
  2. sqlalchemy

Issues

Issue #3002 resolved

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

Sorin Sbarnea
created an issue

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'

Comments (14)

  1. Michael Bayer repo owner

    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.

  2. Sorin Sbarnea reporter

    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.

  3. Michael Bayer repo owner

    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.

  4. Michael Bayer repo owner

    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.

  5. Michael Bayer repo owner

    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.

  6. Sorin Sbarnea reporter

    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)        
    
  7. Sorin Sbarnea reporter

    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.

  8. Michael Bayer repo owner

    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.

  9. Sorin Sbarnea reporter

    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);
    
  10. Michael Bayer repo owner

    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')
    
  11. Michael Bayer repo owner
    • 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>>

  12. Michael Bayer repo owner
    • 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>>

  13. Log in to comment