PostgreSQL extensions and check constraints

[nvictor]

Joe,

Thank you for your feedback earlier. I isolated my issue to one pre-defined extension in PostgreSQL. And I easily reproduced the issue with a new test database by doing:

victor@Victors-MacBook-Air ~> psql postgres
psql (9.4.9)
Type "help" for help.

postgres=# create database test_database;
CREATE DATABASE
postgres=# \q
victor@Victors-MacBook-Air ~> psql test_database;
psql (9.4.9)
Type "help" for help.

test_database=# create extension cube;
ERROR:  extension "cube" already exists
test_database=# create extension earthdistance;
ERROR:  extension "earthdistance" already exists
test_database=# \dx
                                    List of installed extensions
     Name      | Version |   Schema   |                         Description                          
---------------+---------+------------+--------------------------------------------------------------
 cube          | 1.0     | public     | data type for multidimensional cubes
 earthdistance | 1.0     | public     | calculate great-circle distances on the surface of the Earth
 fuzzystrmatch | 1.0     | public     | determine similarities and distance between strings
 hstore        | 1.1     | public     | data type for storing sets of (key, value) pairs
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp     | 1.0     | public     | generate universally unique identifiers (UUIDs)
(6 rows)

test_database=# \q

victor@Victors-MacBook-Air ~> dbtoyaml --version
dbtoyaml 0.7.2
victor@Victors-MacBook-Air ~> dbtoyaml --host=localhost --port=15432 --username=postgres test_database
Traceback (most recent call last):
  File "/opt/boxen/pyenv/versions/3.4.1/bin/dbtoyaml", line 11, in <module>
    load_entry_point('Pyrseas==0.7.2', 'console_scripts', 'dbtoyaml')()
  File "/opt/boxen/pyenv/versions/3.4.1/lib/python3.4/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/opt/boxen/pyenv/versions/3.4.1/lib/python3.4/site-packages/pyrseas/database.py", line 268, in to_map
    self.from_catalog()
  File "/opt/boxen/pyenv/versions/3.4.1/lib/python3.4/site-packages/pyrseas/database.py", line 178, in from_catalog
    self._link_refs(self.db)
  File "/opt/boxen/pyenv/versions/3.4.1/lib/python3.4/site-packages/pyrseas/database.py", line 143, in _link_refs
    db.types.link_refs(db.columns, db.constraints, db.functions)
  File "/opt/boxen/pyenv/versions/3.4.1/lib/python3.4/site-packages/pyrseas/dbobject/dbtype.py", line 367, in link_refs
    assert self[(sch, typ)]
KeyError: ('public', 'earth')

Now when I edit pyrseas.dbobject.dbtype.TypeDict.link_refs to look like the following:

    def link_refs(self, dbcolumns, dbconstrs, dbfuncs):
        """Connect various objects to their corresponding types or domains

        :param dbcolumns: dictionary of columns
        :param dbconstrs: dictionary of constraints
        :param dbfuncs: dictionary of functions

        Fills the `check_constraints` dictionaries for each domain by
        traversing the `dbconstrs` dictionary. Fills the attributes
        list for composite types. Fills the dependent functions
        dictionary for base types.
        """
        for (sch, typ) in dbcolumns:
            if (sch, typ) in self:
                assert isinstance(self[(sch, typ)], Composite)
                self[(sch, typ)].attributes = dbcolumns[(sch, typ)]
                for attr in dbcolumns[(sch, typ)]:
                    attr._type = self[(sch, typ)]
        for (sch, typ, cns) in dbconstrs:
            # NOTE(victor)
            if typ in ['earth']:
                continue
...

it works.

Any help will be appreciated.

Thanks!

[jmafc]

OK, I was able to reproduce the problem. I'll try to take a closer look tomorrow at what's causing it.

[nvictor]

thanks : )

[jmafc]

OK, the problem stems from the queries in dbtype.py and constraint.py. In the former case, we exclude objects that are owned by extensions--see the lines:

             AND t.oid NOT IN (
                 SELECT objid FROM pg_depend WHERE deptype = 'e'
                              AND classid = 'pg_type'::regclass)

In the latter case, we do not have a similar exclusion clause. This was the case when I first added support for extensions (it seems like ages ago) in commit 595f1f26. I'm going to have to see if we can add a similar exclusion clause to the constraints query.

[nvictor]

Joe, you are awesome!

[jmafc]

Thanks, Victor.