Re: [sqlalchemy] "cache lookup failed" on reflection

[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.





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,inget_meta
     meta.reflect(bind=self.get(name))
File"sqlalchemy/sql/schema.py",line 3647,inreflect
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,inrun_callable
returncallable_(self,*args,**kwargs)
File"sqlalchemy/engine/default.py",line 364,inreflecttable
returninsp.reflecttable(table,include_columns,exclude_columns)
File"sqlalchemy/engine/reflection.py",line 563,inreflecttable
     table_name,schema,**table.dialect_kwargs):
File"sqlalchemy/engine/reflection.py",line 369,inget_columns
**kw)
File"<string>",line 2,inget_columns
File"sqlalchemy/engine/reflection.py",line 54,incache
     ret =fn(self,con,*args,**kw)
File"sqlalchemy/dialects/postgresql/base.py",line 2325,inget_columns
     info_cache=kw.get('info_cache'))
File"<string>",line 2,inget_table_oid
File"sqlalchemy/engine/reflection.py",line 54,incache
     ret =fn(self,con,*args,**kw)
File"sqlalchemy/dialects/postgresql/base.py",line 2220,inget_table_oid
     c =connection.execute(s,table_name=table_name,schema=schema)
File"sqlalchemy/engine/base.py",line 914,inexecute
returnmeth(self,multiparams,params)
File"sqlalchemy/sql/elements.py",line 323,in_execute_on_connection
returnconnection._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,inraise_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,indo_execute
     cursor.execute(statement,parameters)
|

--
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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.