Postgres error "SAVEPOINT can only be used in transaction blocks" when used with live_server

[citizen-stig]

I have tests, that I would like to run with live_server from pytest-flask package.

I thought it works ok, but only for single request. Full repo with working example, failing test

Here are some pieces of the code:

# models.py
class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True, nullable=False)

# view
@blog.route('/categories')
def list_categories():
    return '<br/>'.join(str(x.id) + ': ' + x.name for x in models.Category.query)

factories from conftest.py

@pytest.fixture(scope='function')
def base_factory(db_session):
    class BaseFactory(factory.alchemy.SQLAlchemyModelFactory):
        class Meta:
            abstract = True
            sqlalchemy_session = db_session
            sqlalchemy_session_persistence = 'flush'
    return BaseFactory

@pytest.fixture(scope='function')
def category_factory(base_factory):
    class CategoryFactory(base_factory):
        class Meta:
            model = models.Category
        name = factory.Sequence(lambda n: u'Category %d' % n)
    return CategoryFactory

Test itself:

from flask import url_for
import requests

from demo_app import models

def test_list_categories(live_server, category_factory):
    categories = category_factory.create_batch(5)

    assert models.Category.query.count() == 5

    url = url_for('blog.list_categories', _external=True)

    response_1 = requests.get(url)

    assert response_1.status_code == 200
    data_1 = response_1.content.decode('utf-8')
    for category in categories:
        assert category.name in data_1

    assert models.Category.query.count() == 5

    response_2 = requests.get(url)

    assert response_2.status_code == 200
    data_2 = response_2.content.decode('utf-8')
    print(data_2)
    for category in categories:
        assert category.name in data_2

I'm getting error on line assert models.Category.query.count() == 5, or if I comment it out, data_2 data returned by the server is empty.

The error:

self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x104135780>
cursor = <cursor object at 0x1040ea900; closed: -1>
statement = 'SAVEPOINT sa_savepoint_23', parameters = {}
context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x104321048>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.InternalError: (psycopg2.errors.NoActiveSqlTransaction) SAVEPOINT can only be used in transaction blocks
E       
E       [SQL: SAVEPOINT sa_savepoint_23]
E       (Background on this error at: http://sqlalche.me/e/2j85)

Full stack trace

Could you please help me, I don't know where to start to investigate this.

[jeancochrane]

This is a pretty interesting problem. I don't have much experience with live_server so I don't really know how it works, but one thing I'm curious about is how it handles SQLAlchemy connections in the process that runs the server (which I'm guessing gets forked from the pytest process). The SQLAlchemy docs are pretty clear that connections shouldn't be shared across processes:

The SQLAlchemy Engine object refers to a connection pool of existing database connections. So when this object is replicated to a child process, the goal is to ensure that no database connections are carried over.

This makes me wonder whether live_server is doing some work under the hood to dispose of the connection, which could result in the confusion here where a transaction context is expected but not found. Alternatively, perhaps live_server isn't doing anything at all to handle the shared connection, and we need to be on the lookout for potential multiprocessing on our end and proactively create a new transactional context. Either way, the pytest-flask docs don't say much about this, so we'd probably need to take a look at the internals to see what's going on.

Another interesting lead is that I can't actually replicate the error you're seeing. Instead, I see an error that gets raised when the live_server thread tries to run model.Category.query:

*** sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) SSL error: decryption failed or bad record mac

This SO thread seems to see a similar error in a multiprocessing context.

Sorry if that isn't a very helpful answer at this stage. I should have some more time next week to dig into this more deeply if you're still having problems.

[citizen-stig]

@jeancochrane Thanks for such detailed explanation, and any help is appreciated!

That is strange, that error is different. I used pipenv, so all exact versions should be presented in Pipenv.lock I run Postgresl on docker, maybe I should build docker-compose environment, so it will be easier to reproduce.

[jeancochrane]

A Docker environment would be really helpful!

…

On Mon, Apr 22, 2019 at 3:19 PM Nikolai Golub ***@***.***> wrote: @jeancochrane <https://github.com/jeancochrane> Thanks for such detailed explanation, and any help is appreciated! That is strange, that error is different. I used pipenv, so all exact versions should be presented in Pipenv.lock I run Postgresl on docker, maybe I should build docker-compose environment, so it will be easier to reproduce. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#17 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADMDUGSW7JXNRWBWQPIKCL3PRYFSTANCNFSM4HGCOUPQ> .

[samuelhwilliams]

I'm trialling out this plugin to see if I can get tests isolated in transactions in a repo I work with. After a few hours of fiddling with the main unit tests, and getting them all passing, I'm now onto the functional tests using the live_server. I'm getting the same error as OP. :(

[citizen-stig]

@jeancochrane , I've finally managed to create docker environment, repository is updated:
https://github.com/citizen-stig/flask-pytest-samples

just use docker-compose up and error will be printed in console

[jeancochrane]

Awesome, thanks @citizen-stig! I'll have time to do some testing this weekend.

[msharaf315]

any upadtes on this im facing a similar issue?