python sqlalchemy + postgresql 程序冻结 [英] python sqlalchemy + postgresql program freezes

问题描述

我遇到了一个奇怪的情况.我正在为我的程序编写一些测试用例.该程序被编写为根据偏好在 sqllite 或 postgresqul 上工作.现在我正在使用 unittest 编写我的测试代码.基本上我在做什么:

I've ran into a strange situation. I'm writing some test cases for my program. The program is written to work on sqllite or postgresqul depending on preferences. Now I'm writing my test code using unittest. Very basically what I'm doing:

def setUp(self):
    """
        Reset the database before each test.
    """
    if os.path.exists(root_storage):
        shutil.rmtree(root_storage)
    reset_database()
    initialize_startup()
    self.project_service = ProjectService()
    self.structure_helper = FilesHelper()
    user = model.User("test_user", "test_pass", "[e-mail]",
                       True, "user")
    self.test_user = dao.store_entity(user) 

在设置中，我删除任何存在的文件夹(由某些测试创建)，然后我重置我的数据库(基本上删除表级联)然后我再次初始化数据库并创建一些将用于测试的服务.

In the setUp I remove any folders that exist(created by some tests) then I reset my database (drop tables cascade basically) then I initialize the database again and create some services that will be used for testing.

def tearDown(self):
    """
        Remove project folders and clean up database.
    """
    created_projects = dao.get_projects_for_user(self.test_user.id)
    for project in created_projects:
        self.structure_helper.remove_project_structure(project.name)
    reset_database()

除创建服务外，拆解做同样的事情，因为这个测试模块与其他模块属于同一套件，我不希望某些测试留下一些东西.

Tear down does the same thing except creating the services, because this test module is part of the same suite with other modules and I don't want things to be left behind by some tests.

现在我所有的测试都用 sqllite 运行良好.使用 postgresql 我遇到了一个非常奇怪的情况:在执行的某个时刻，实际上与运行之间的差异很小(例如一两个额外的调用)，程序只是停止了.我的意思是没有错误产生，没有异常抛出，程序只是停止.

Now all my tests run fine with sqllite. With postgresql I'm running into a very weird situation: at some point in the execution, which actually differs from run to run by a small margin (ex one or two extra calls) the program just halts. I mean no error is generated, no exception thrown, the program just stops.

现在我唯一能想到的是，不知何故我忘记了在某处打开的连接，并且在它超时之后发生了一些事情.但是我有很多人脉，所以在我开始研究所有这些代码之前，我会很感激一些建议/意见.

Now only thing I can think of is that somehow I forget a connection opened somewhere and after I while it timesout and something happens. But I have A LOT of connections so before I start going trough all that code, I would appreciate some suggestions/ opinions.

什么会导致这种行为?从哪里开始寻找?

What could cause this kind of behaviour? Where to start looking?

问候，博格丹

推荐答案

基于 PostgreSQL 的应用程序冻结，因为 PG 相当积极地锁定表，特别是如果在挂起的事务中打开任何连接，它将不允许 DROP 命令继续，这以任何方式访问过该表(包括 SELECT).

PostgreSQL based applications freeze because PG locks tables fairly aggressively, in particular it will not allow a DROP command to continue if any connections are open in a pending transaction, which have accessed that table in any way (SELECT included).

如果您使用的是 unix 系统，命令ps -ef | grep 'post'"将显示所有 Postgresql 进程，您将看到当前命令的状态，包括挂起的DROP TABLE"或不管是什么都是冰冻的.如果您从 pg_stat_activity 视图中选择，您也可以看到它.

If you're on a unix system, the command "ps -ef | grep 'post'" will show you all the Postgresql processes and you'll see the status of current commands, including your hung "DROP TABLE" or whatever it is that's freezing. You can also see it if you select from the pg_stat_activity view.

所以关键是确保没有挂起的事务仍然存在——这意味着在 DBAPI 级别关闭任何结果游标，并且当前打开的任何连接都有 rollback() 调用它，或以其他方式明确关闭.在 SQLAlchemy 中，这意味着任何带有挂起行的结果集(即 ResultProxy)都已完全耗尽，并且任何 Connection 对象都已被 close()d，它将它们返回到池中并在底层 DBAPI 连接上调用 rollback().您需要确保存在某种无条件拆卸代码，以确保在发出任何 DROP TABLE 类型的命令之前发生这种情况.

So the key is to ensure that no pending transactions remain - this means at a DBAPI level that any result cursors are closed, and any connection that is currently open has rollback() called on it, or is otherwise explicitly closed. In SQLAlchemy, this means any result sets (i.e. ResultProxy) with pending rows are fully exhausted and any Connection objects have been close()d, which returns them to the pool and calls rollback() on the underlying DBAPI connection. you'd want to make sure there is some kind of unconditional teardown code which makes sure this happens before any DROP TABLE type of command is emitted.

至于我有很多联系"，您应该控制住它.当 SQLA 测试套件运行其 3000 个测试时，我们确保我们完全控制连接，并且通常一次只打开一个连接(但是，在 Pypy 上运行的某些行为仍然 原因与 PG 息息相关……这很艰难).有一个名为 AssertionPool 的池类，您可以使用它来确保一次只签出一个连接，否则会引发信息性错误(显示它被签出的位置).

As far as "I have A LOT of connections", you should get that under control. When the SQLA test suite runs through its 3000 something tests, we make sure we're absolutely in control of connections and typically only one connection is opened at a time (still, running on Pypy has some behaviors that still cause hangs with PG..its tough). There's a pool class called AssertionPool you can use for this which ensures only one connection is ever checked out at a time else an informative error is raised (shows where it was checked out).

这篇关于python sqlalchemy + postgresql 程序冻结的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！