"KeyError" for partitioned table with indexes (including constraints)

[crimean-celica]

Steps to reproduce:

1. test database with test table (plus primary key):

1.1 new database:

postgres=# create database a1;
CREATE DATABASE

1.2. new table:

a1=# create table a1 ( id int , key int ) partition by list ( key );
CREATE TABLE
a1=# create table a1_1 partition of a1 for values in ( 1 );
CREATE TABLE
a1=# create table a1_2 partition of a1 for values in ( 2 );
CREATE TABLE

Here I can export schema with no errors.

1.3. primary key:

a1=# alter table a1 add constraint pk_a1 primary key ( id , key );
ALTER TABLE

2. trying to export schema:

$ dbtoyaml -H localhost -p 5432 a1 -U postgres
Traceback (most recent call last):
  File ".local/bin/dbtoyaml", line 11, in <module>
    sys.exit(main())
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 443, in to_map
    self.from_catalog(True)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 349, in from_catalog
    self.db = self.Dicts(self.dbconn, single_db)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 97, in __init__
    self.tables = ClassDict(dbconn)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbobject/__init__.py", line 633, in __init__
    self._from_catalog()
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbobject/table.py", line 830, in _from_catalog
    table = self[(sch, tbl)]
KeyError: ('public', 'a1_1_pkey')

The same results I obtained when removed the primary key and added a regular index. I.e. right after:

a1=# alter table a1 drop constraint pk_a1;
ALTER TABLE

I had no error but after:

a1=# create index ix_a1 on a1 ( id );
CREATE INDEX

I got:

$ dbtoyaml -H localhost -p 5432 a1 -U postgres
Traceback (most recent call last):
  File ".local/bin/dbtoyaml", line 11, in <module>
    sys.exit(main())
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 443, in to_map
    self.from_catalog(True)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 349, in from_catalog
    self.db = self.Dicts(self.dbconn, single_db)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/database.py", line 97, in __init__
    self.tables = ClassDict(dbconn)
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbobject/__init__.py", line 633, in __init__
    self._from_catalog()
  File "/home/nix/.local/lib/python2.7/site-packages/pyrseas/dbobject/table.py", line 830, in _from_catalog
    table = self[(sch, tbl)]
KeyError: ('public', 'a1_1_id_idx')

I.e. seems, any index (including primary ley/unique constraints) leads to such issue. Thanks

[jmafc]

I have confirmed the issue against a PG 11 set-up. I appears that in PG 11, the definition of the pg_inherits catalog was changed to include index inheritance (see https://www.postgresql.org/docs/11/catalog-pg-inherits.html and compare it to the docs/10 version). Our initial (and incomplete) support for partitioned tables was done (as I recall) for PG 10 and essentially nothing was done after that. The problem starts with the query in the inhquery function of table.py. It now returns two extra rows in your case (the pkey or idx rows). This could be fixed by modifying that query by JOINing to pg_class ON inhrelid and adding a WHERE to restrict the result to relkind = 'r', e.g.,

SELECT inhrelid::regclass AS sub,
                         inhparent::regclass AS parent, inhseqno
                  FROM pg_inherits i JOIN pg_class c ON inhrelid = c.oid
                  WHERE relkind = 'r'
                  ORDER BY 1, 3

This does fix your immediate problem but I'm not sure if others will surface.