Query that behaves badly is:
session.query(MyTable).join(MyOtherTable, and_(
ParentMyOtherTable.id == MyTable.parent_id,
ParentMyOtherTable.volume_id == MyTable.volume_id
))
On Wednesday, 24 October 2018 10:12:43 UTC+2, Stanisław Skonieczny wrote:
>
> 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]> 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].
>> > 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.
>>
>
--
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.
