res.ftype(column_number) returns corrupted answer for data types with high OIDs

Issue #187 resolved
Mina Naguib
created an issue

The postgresql documentation states "The oid type is currently implemented as an unsigned four-byte integer". Its C typedef in postgres_ext.h is:

typedef unsigned int Oid;

We've hit a case in our DB where a data type (hstore) in pg_type has OID 2163584298, which is below 2^32 but slightly higher than 2^31

When we do a select on a table which returns columns of that type, res.ftype( column_number ) returns a corrupted negative number

I believe the bug is in pg_result.c function pgresult_ftype:

     ...
     return INT2NUM(PQftype(result, i));

This takes the 32-bit unsigned Oid and passes it to INT2NUM which treats it as a signed int, causing the corruption.

The fix I've applied locally is very simply to call UINT2NUM instead. #ftype then returns the correct positive Oid (which is needed in rails as it joins it on data collected from pg_type)

Comments (10)

  1. Michael Granger repo owner

    I couldn't come up with a spec that tested this other than creating an extension that set its own type Oid, but I'll keep trying. In the meantime, it's a fairly straightforward change, so I'm calling it fixed.

  2. Mina Naguib reporter

    Cool. Thanks. Any chance of a release ? The git repo doesn't have a Gemfile, so it's near impossible to use bundler's ":git => url" syntax to depend on the project from source instead of a public release.

    Also regarding actually replicating the issue, you can create a table in postgres specifying "with oids". Insert 2.2 billion rows (doesn't have to be cumulative - delete/truncate every few), then issue a "create type".

  3. Mina Naguib reporter

    Concretely:

    psql> create table test (x char(1)) with oids;
    

    Then

     $ (yes | head -n 2147483647; echo "\.") | psql -c "\copy test from stdin"
     # This will take a while. in another connection "\dt+ test" to check progress - finishes around 90GB
    

    Then

      psql> create type foo as enum ('foo', 'bar');
      psql> select oid, typname from pg_type order by oid desc limit 1;
      // Verify high OID above for type foo
      psql> insert into test2 values('bar');
    

    Finally "select f from test2" with ruby-pg and check #ftype(1)

  4. Michael Granger repo owner

    Okay. I hesitate to try to do this in the regular unit test suite, but thanks for the example.

    I've pushed a prerelease up to Rubygems; you should be able to use it either by passing --pre to the gem command, or specifying the 0.17.2.pre.546 version in your Gemfile. Let me know how it goes.

  5. Log in to comment