"cache lookup failed" on reflection

[Matt Smith]

Hi sqlalchemy!

When using sqlalchemy (core) 1.0.8 with redshift-sqlalchemy 0.4.1, I encountered the following exception:

InternalError: (psycopg2.InternalError) cache lookup failed for relation 3262644

This is the query it failed on:

SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (pg_catalog.pg_table_is_visible(c.oid))
AND c.relname = %(table_name)s AND c.relkind in ('r', 'v', 'm', 'f')

Our hypothesis is that another process operating on the same Redshift cluster (which runs a table shuffling process and deletes tables), is causing our code to error during schema reflection. Does this sound correct to you, and if so, is there a way for our code to mitigate this issue?

Thanks, Matt Smith

Here's the relevant portion of the stack trace:

... snip ...
    engine, meta = ENGINES.get('redshift'), ENGINES.get_meta('redshift')
  File "ourcode.py", line 38, in get_meta
    meta.reflect(bind=self.get(name))
  File "sqlalchemy/sql/schema.py", line 3647, in reflect
    Table(name, self, **reflect_opts)
  File "sqlalchemy/sql/schema.py", line 416, in __new__
    metadata._remove_table(name, schema)
  File "sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "sqlalchemy/sql/schema.py", line 411, in __new__
    table._init(name, metadata, *args, **kw)
  File "sqlalchemy/sql/schema.py", line 484, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "sqlalchemy/sql/schema.py", line 496, in _autoload
    self, include_columns, exclude_columns
  File "sqlalchemy/engine/base.py", line 1477, in run_callable
    return callable_(self, *args, **kwargs)
  File "sqlalchemy/engine/default.py", line 364, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "sqlalchemy/engine/reflection.py", line 563, in reflecttable
    table_name, schema, **table.dialect_kwargs):
  File "sqlalchemy/engine/reflection.py", line 369, in get_columns
    **kw)
  File "<string>", line 2, in get_columns
  File "sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "sqlalchemy/dialects/postgresql/base.py", line 2325, in get_columns
    info_cache=kw.get('info_cache'))
  File "<string>", line 2, in get_table_oid
  File "sqlalchemy/engine/reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "sqlalchemy/dialects/postgresql/base.py", line 2220, in get_table_oid
    c = connection.execute(s, table_name=table_name, schema=schema)
  File "sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)

[Mike Bayer]

On 02/23/2016 11:58 AM, Matt Smith wrote:
> Hi sqlalchemy!
>
> When using sqlalchemy (core) 1.0.8 with redshift-sqlalchemy 0.4.1, I
> encountered the following exception:
>
> |
> InternalError:(psycopg2.InternalError)cache lookup failed forrelation
> 3262644
> |
>
> This is the query it failed on:
>
> |
> SELECT c.oid
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid =c.relnamespace
> WHERE (pg_catalog.pg_table_is_visible(c.oid))

> AND c.relname =%(table_name)s AND c.relkind in('r','v','m','f')


this error is being raised by the driver and I'd guess is some
redshift-specific error, I've certainly never seen anything like that
from Postgresql.

> |
>
> Our hypothesis is that another process operating on the same Redshift
> cluster (which runs a table shuffling process and deletes tables), is
> causing our code to error during schema reflection. Does this sound
> correct to you, and if so, is there a way for our code to mitigate this
> issue?

you'd have to make sure your code doesn't attempt to reflect the
database while other processes are modifying it. I'd not expect that
to work on any kind of system. Database schema mutability is not
something intended to tolerate concurrency.

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.