On Wed, Oct 24, 2018 at 4:12 AM Stanisław Skonieczny
<stanislaw.skoniec...@gmail.com> 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):
OK so this is not something you can see happening then? that is, when you say:
>>> ParentMyTable.volume_id.comparator.table
Table('my_table', MetaData(bind=None), ...
you didn't actually sit at a pdb and see that happen, right? you're
just seeing that SQL when it's under load. What exactly is an
"upgrade"? Are you using live module re-loading? Applications that
use live reloading aren't generally reliable, better to restart the
entire interpreter. also, if the Python modules are changing
underneath while the interpreter is trying to load the application,
that will lead to random errors too.
>
>
>
> 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
>> <stanislaw....@gmail.com> 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 sqlalchemy+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@googlegroups.com.
>> > 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 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.
--
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 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.
