crash with temp table

[tbussmann]

It seems dbtoyaml is not ignoring temp tables (or the internal schema used for temp tables) when enumerating the schema and fails in processing while such a table exists. This is quite strange as the temp table should not be visible in other sessions at all.

Using pyrseas 0.7.3 on OSX 10.11.6 with PostgreSQL 9.5.5 on an empty database:

bussmann$ dbtoyaml bussmann
extension plpgsql:
  description: PL/pgSQL procedural language
  owner: postgres
  schema: pg_catalog
  version: '1.0'
schema public:
  description: standard public schema
  owner: postgres
  privileges:
  - PUBLIC:
    - all
  - postgres:
    - all

• Now in a new terminal, open psql, connect to the db and create a session temporary table: CREATE TEMP TABLE test(col INTEGER); - leave the session open.

• Then, run dbtoyaml again:

  bussmann$ dbtoyaml bussmann
  Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 11, in <module>
  load_entry_point('Pyrseas==0.7.3', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/dbtoyaml.py", line 49, in main
  dbmap = db.to_map()
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/database.py", line 269, in to_map
  self.from_catalog()
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/database.py", line 179, in from_catalog
  self._link_refs(self.db)
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/database.py", line 137, in _link_refs
  db.schemas.link_refs(db, copycfg)
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/dbobject/schema.py", line 272, in link_refs
  link_one(targ, type_, keys)
  File "/usr/local/lib/python3.5/site-packages/Pyrseas-0.7.3-py3.5.egg/pyrseas/dbobject/schema.py", line 250, in link_one
  schema = self[objkeys[0]]
  KeyError: 'pg_temp_2'

[jmafc]

Hmm... This seems to have been broken in the r0.5 to r0.6 timeframe. It happens in r0.6 but in r0.5, dbtoyaml shows the schema pg_temp_2 (as well as schema pg_toast_temp_2). In fact, 77039da says "Exclude pg_temp_nnn/pg_toast_temp_nnn schemas, not just xxx_temp_1. Fixes #37." but it seems we didn't test the side effect of the exclusions properly.

[jmafc]

I believe we need to work on a better exclusion clause in SchemaDict.query because we'll probably have to propagate the exclusions elsewhere.

[jmafc]

I have a fix for the specific case of a TEMP TABLE described above:

@ -631,6 +631,7 @@ QUERY_PRE93 = \
             JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
             LEFT JOIN pg_tablespace t ON (reltablespace = t.oid)
        WHERE relkind in ('r', 'S', 'v')
+             AND relpersistence != 't'
              AND (nspname != 'pg_catalog'
                   AND nspname != 'information_schema')
        ORDER BY nspname, relname"""
@@ -657,6 +658,7 @@ class ClassDict(DbObjectDict):
                 JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
                 LEFT JOIN pg_tablespace t ON (reltablespace = t.oid)
            WHERE relkind in ('r', 'S', 'v', 'm')
+                 AND relpersistence != 't'
                  AND (nspname != 'pg_catalog'
                       AND nspname != 'information_schema')
            ORDER BY nspname, relname"""

IOW, exclude any temporary tables from the ClassDict._from_catalog queries. However, if I create an index on the TEMP table, then a similar KeyError occurs, so I have to investigate further to cover all the bases.

[tbussmann]

wow, thanks for the super quick support. I've tested the patch and it seems to work properly for temp Tables, Sequences, Indexes on Temp Tables and Temp Views. If old versions shall still be supported, the QUERY_PRE91 in could be altered to include a AND NOT relistemp which was available in 8.4 and 9.0 according to the docs. But this would break < 8.4 support. However, according to the README.rst only PostgreSQL >= 9.2 is supported anyhow.

[jmafc]

Actually, there's a mistake. I changed QUERY_PRE93 in r0.7 but git merge (I think) modified QUERY_PRE91 in master and deptrack (and I didn't catch it when looking at the diffs).