It uses threads. It may be correlated with high load, as it usually happens
when process is run during upgrade. Schema of the table is as follows (I
have removed unrelated tables and fields):
from sqlalchemy import Column, BigInteger, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import aliased
Base = declarative_base()
COMMON_ARGS = {'schema': 'sf'}
class _MyOtherTableBase(object):
volume_id = Column(BigInteger, nullable=False)
class MyOtherTable(_MyOtherTableBase, Base):
__tablename__ = 'my_other_table'
__table_args__ = COMMON_ARGS
id = Column(BigInteger, nullable=False, primary_key=True)
parent_id = Column(ForeignKey('sf.my_other_table.id'))
class _MyTableBase(object):
volume_id = Column(BigInteger, nullable=False)
class MyTable(_MyTableBase, Base):
__tablename__ = 'my_table'
__table_args__ = COMMON_ARGS
id = Column(BigInteger, primary_key=True, nullable=False)
parent_id = Column(ForeignKey('sf.my_other_table.id'))
ParentMyOtherTable = aliased(MyOtherTable)
# when process produces broken sql,
ParentMyOtherTable.volume_id.comparator.table is instance of Table instead of
Alias
We use following code to setup engine and session:
# on process start we do:
engine_kwargs = {}
engine_kwargs.setdefault('poolclass', QueuePool)
engine_kwargs.setdefault('isolation_level', ISOLATION_LEVEL_READ_COMMITTED)
engine = create_engine('postgresql://something', **engine_kwargs)
session_factory = sessionmaker(bind=engine)
scoped_session = scoped_session(self._session_factory)
# and later in some thread:
session = scoped_session()
On Tuesday, 23 October 2018 18:15:43 UTC+2, Mike Bayer wrote:
>
> On Tue, Oct 23, 2018 at 9:40 AM Stanisław Skonieczny
> <[email protected] <javascript:>> wrote:
> >
> > Sometimes sqlalchemy produces wrong sql-s. Usually everything works
> fine, but sometimes process enters somehow wrong state and then this
> process starts producing wrong queries over and over again. Exception is as
> follows:
> >
> >
> > (psycopg2.ProgrammingError) invalid reference to FROM-clause entry for
> table "my_table"
> > LINE 2: ... my_table_1.id = sf.my_other_table.parent_id AND
> sf.my_table.volume_id = 123
> > ^
> > HINT: Perhaps you meant to reference the table alias "my_table_1".
> >
> > It uses real table name instead of an aliased.
> > It looks like that this is caused by wrong comparator table on aliased
> table:
> >
> > >>> ParentMyTable.volume_id.comparator.table
> > Table('my_table', MetaData(bind=None), ...
> >
> > ParentDirCurrent is initialized just after process starts with:
> > ParentMyTable = aliased(MyTable)
> >
> > In processes that produces correct sqls, it is:
> >
> > >>> ParentDirCurrent.volume_id.comparator.table
> > <sqlalchemy.sql.selectable.Alias at 0x7fb991f477b8; %(140434994395064
> dir_current)s>
> >
> > We use python 3.6, pyinstallered binaries and sqlalchemy==1.1.12.
> >
> > Usually restarting the process helps, but not always. Could you confirm
> that comparator.table on aliased table should also be aliased table?
> > Is it already fixed in some newer version?
>
> There's no way I can comment on what you are seeing without the
> mappings, configuation, and usage example which is producing the
> problem. Additionally when you say it is "sometimes" , is
> that...certain code paths? or under load? are you using threads?
> things like that. There's lots of reasons things can go wrong and
> without a specific reproducer case I can't say much.
>
>
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> > ---
> > 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
