I have seen that when I connected to a process that produced wrong sql-s. I used pyrasite to attach to a live process. I compared sql-alchemy structures inside with a process that produced correct sql's. That is the difference I have found: in the process that produces good sql-s, comparator.table was an instance of Alias. In the wrong process it was instance of Table.
System was not under heavy load when it produced wrong sql-s, but it might have been under heavy load when it was started (because many other services was started at same time). We do not use live reloading. After installing new sources entire process is restarted. But we use pyinstaller, This problem happens rarely: it happened 4 times, but it did not happen many thousands times (I count processes that has been started). In 3 cases restarting process helped. But in 1 case restarting process did not help and sql-s were still wrong after restart. On Wednesday, 24 October 2018 20:42:46 UTC+2, Mike Bayer wrote: > > On Wed, Oct 24, 2018 at 4:12 AM Stanisław Skonieczny > <[email protected] <javascript:>> 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 > >> <[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] <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.
