On Fri, Jun 30, 2017 at 11:52 AM, Anders Steinlein <and...@e5r.no> wrote:
> After this switch, we're seeing sporadic ObjectDeletedError exceptions
> illustrated by the following simple lines of code:
>
> @newsletters.route('/create/', methods=['GET', 'POST'])
> @login_required
> def create():
> newsletter = Newsletter()
> # db is Flask-SQLAlchemy's SQLAlchemy object instance
> db.session.add(newsletter)
> # Commit explicitly to ensure we have a mid to redirect to.
> db.session.commit()
> return redirect(url_for('.set_recipients', id=newsletter.mid))
>
> Newsletter is a regular SQLAlchemy ORM Base, where mid is a plain Integer
> column as a primary key (i.e. a serial in the database).
>
> Accessing newsletter.mid there throws an exception once in every 50-ish
> executions or some such: Instance '<Newsletter at 0x7fbba0571908>' has been
> deleted, or its row is otherwise not present.
>
> The database row is surely not deleted.
>
> This is a multi-tenant application, whereby pretty much the first thing we
> do in every request is db.session.execute('SET search_path TO {.username},
> public'.format(current_user)).
>
> I've set _use_threadlocal = True on the connection pool, as my thinking
> initially was that we could be getting a different connection object back
> from the pool after the commit. Could this still be the case? And/or the
> search_path not being the same after the commit?
that would be exactly what's happening here, there's not really a way
guarantee getting the same connection back from two checkouts from a
pool. the "threadlocal" thing on the pool has to do with concurrent
checkouts, not two checkouts/checkins in serial, and it's not a flag
I'd recommend these days for anything.
if you need search_path set on the connection, there's two ways to go
about this:
1. ensure that happens for every checkout using a pool event or engine
connect event.
2. keep the search path set at the request level, but then bind the
Session for that request as well:
connection_for_my_request = engine.connect()
connection_for_my_request.execute("set search path....")
session_for_my_request = Session(bind=connection_for_my_request)
>
> Even so, if the search_path was reset to public, I would presume the row
> would still be found -- at least that is the case in a vanilla psql shell,
> as all tables are present in the public schema and inherited in each
> tenant's schema (thus making PostgreSQL search all inherited tables across
> all schemas).
is the row in question local to a tenant or not? if the row here is
in "public" and the query has no dependency on the tenant schema
then...you need to figure out first the nature of this "object
deleted" error, put it in a try/except and in the except, take the
Session.connection() and interrogate it for current schema, whether or
not the row can be found, etc. dump it all to the log.
stack trace for the ObjectDeletedError is also important here as that
would show where it's actually happening.
>
> No master/slave is involved, just a single database server. The SQLAlchemy
> pool is the default QueuePool with only default options (apart from
> _use_threadlocal as mentioned above).
>
> Any guidance to figuring this out would be much appreciated. And thanks for
> an awesome library!
>
> Relevant package versions:
> SQLAlchemy==1.1.11
> Flask-SQLAlchemy==2.1
> psycopg2==2.6.2
> uWSGI==2.0.15
> gevent==1.2.2
> greenlet==0.4.12
>
>
> Best,
> Anders
>
> --
> 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.
