`create_all` failing with `data type bigint has no default operator class for access method "gist"`

[tomthorogood]

I am trying to integrate my application with postgresql-audit, but when my tests run sqlalchemy's create_all on our test database instance, I get the following error:

E       sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) data type bigint has no default operator class for access method "gist"
E       HINT:  You must specify an operator class for the index or define a default operator class for the data type.
E       
E       [SQL: 
E       CREATE TABLE transaction (
E               id BIGSERIAL NOT NULL, 
E               native_transaction_id BIGINT, 
E               issued_at TIMESTAMP WITHOUT TIME ZONE, 
E               client_addr INET, 
E               PRIMARY KEY (id), 
E               CONSTRAINT transaction_unique_native_tx_id EXCLUDE USING gist (native_transaction_id WITH =, tsrange(issued_at - INTERVAL '1 hour', issued_at) WITH &&)
E       )
E       
E       ]
E       (Background on this error at: http://sqlalche.me/e/f405)

I made sure that our test DB is creating the btree_gist extension:

@pytest.fixture(scope='session', autouse=True)
def database(db_proc, _app_with_context):
    """
    Create a Postgres database for the tests, and drop it when the tests are done.
    """
    jan = janitor.DatabaseJanitor(DB_USER, DB_HOST, DB_PORT, DB_NAME, DB_VERSION)
    jan.init()
    app.db.session.execute('CREATE EXTENSION IF NOT EXISTS btree_gist')  # This exectes
    app.db.create_all()  # This fails with the above error
    yield
    jan.drop()

There is no mention of any additional requirements in the README for this package, so I'm not sure what could be wrong. It feels like every audit history plugin is a dead end, and I was really hopeful this would provide a solution for us.

[jensenja]

Hi,

Not sure if it helps you, but I ran into a similar issue. I'm not running db.create_all() in a test harness, but I am adding some pre-populated data with my own script that does call db.create_all() and I was getting the exact same error. I'm running Postgres in Docker:

root@5535163b72a5:/# psql -U postgres
psql (9.6.16)

My db name is dftdb.

postgres=# DROP DATABASE dftdb;
DROP DATABASE
postgres=# CREATE DATABASE dftdb OWNER dftuser;
CREATE DATABASE
postgres=#

The solution does involve installing the btree_gist extension. You can check if you have the extension available by running SELECT pg_available_extensions(); in psql. If you don't and you're running Postgres on bare metal you may need to install the related contrib package for your version of Postgres on your OS. The Docker postgres:9 tag does have this extension available though.

Once the DB is created you connect to it and can then run \dx to see which extensions are enabled for the database:

postgres=# \c dftdb
You are now connected to database "dftdb" as user "postgres".
dftdb=#
dftdb=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

dftdb=#

From here I just installed the extension and ran \dx again to verify it was installed on the database.

dftdb=# CREATE EXTENSION btree_gist;
CREATE EXTENSION
dftdb=#
dftdb=# \dx
                           List of installed extensions
    Name    | Version |   Schema   |                  Description
------------+---------+------------+-----------------------------------------------
 btree_gist | 1.2     | public     | support for indexing common datatypes in GiST
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

dftdb=#

After that I didn't get any more errors with db.create_all(). Hope this helps.

[willbackslash]

You need to install the gist extension for postgres before:
CREATE EXTENSION btree_gist;