#355 Postgres "idle in transaction"

Reporter rypervenche
Owner MattJ
Created
Updated
Stars ★★★ (3)  
Tags
  • Priority-Medium
  • Milestone-0.10
  • Type-Defect
  • Status-Accepted
  1. rypervenche on

    *What steps will reproduce the problem?* 1. Create PostgreSQL database and user. 2. Add credentials to /etc/jabber/prosody.cfg.lua 3. Start Prosody with /etc/init.d/prosody start *What is the expected output? What do you see instead?* When I add a user using prosodyctl and also in "ps faux" I see nu ~ # prosodyctl adduser test@ryper.org WARNING: there is already a transaction in progress Enter new password: Retype new password: nu ~ # prosodyctl deluser test@ryper.org WARNING: there is already a transaction in progress nu ~ # ps faux | grep prosody | head -1 postgres 2627 0.0 0.1 62388 4980 ? Ss 17:31 0:00 \_ postgres: prosody prosody ::1(36105) idle in transaction *What version of the product are you using? On what operating system?* Prosody 0.8.2 PostgreSQL 9.2.4 Gentoo Linux (Gentoo Base System release 2.2) *Please provide any additional information below.* nu ~ # equery g net-im/prosody-0.8.2 * Searching for prosody0.8.2 in net-im ... * dependency graph for net-im/prosody-0.8.2 `-- net-im/prosody-0.8.2 ~amd64 `-- net-im/jabber-base-0.01 (net-im/jabber-base) amd64 `-- dev-lang/lua-5.1.5 (>=dev-lang/lua-5.1) amd64 `-- net-dns/libidn-1.26 (>=net-dns/libidn-1.1) amd64 `-- dev-libs/openssl-1.0.1c (>=dev-libs/openssl-0.9.8) amd64 `-- dev-lua/luasocket-2.0.2 (dev-lua/luasocket) amd64 `-- dev-lua/luasec-0.4.1 (dev-lua/luasec) amd64 `-- dev-lua/luaexpat-1.2.0-r1 (dev-lua/luaexpat) amd64 `-- dev-lua/luafilesystem-1.5.0 (dev-lua/luafilesystem) amd64 `-- dev-lua/luadbi-0.5-r2 (>=dev-lua/luadbi-0.5) amd64 [mysql] `-- dev-lua/luaevent-0.3.1 (dev-lua/luaevent) amd64 `-- dev-lua/lua-zlib-0.2 (dev-lua/lua-zlib) amd64 [ net-im/prosody-0.8.2 stats: packages (12), max depth (1) ] postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_activity LIMIT 1; -[ RECORD 1 ]----+---------------------------------------------- datid | 18460 datname | prosody pid | 2627 usesysid | 18459 usename | prosody application_name | client_addr | ::1 client_hostname | client_port | 36105 backend_start | 2013-07-03 17:31:56.955265-05 xact_start | 2013-07-03 17:31:56.966189-05 query_start | 2013-07-03 17:31:56.98619-05 state_change | 2013-07-03 17:31:56.986273-05 waiting | f state | idle in transaction query | DEALLOCATE "dbd-postgresql-00000000000000002"

  2. rypervenche on

    It looks like Matthew Wild acknowledged this issue in a Google Groups message, which can be found here: https://groups.google.com/forum/#!msg/prosody-dev/tCZSZYDDr7w/fkcwZyvvWFAJ

  3. otte.mm on

    I've got a similar behavior on my server with prosody 0.9.4 I'm using Arch Linux: aur/lua51-ldbi 0.5.0-1 [installed] (0) PostgreSQL and SQLite bindings to Lua 5.1 community/prosody 0.9.4-3 [installed] Lightweight and extensible Jabber/XMPP server written in Lua extra/postgresql 9.3.5-1 [Installiert] A sophisticated object-relational DBMS is there some kind of workaround? Like getting the data into a provided and functional db backend, like sqlite? I assume that the migrator won't succeed if prosody itself has problems with postgres...

  4. MattJ on

    As far as I know you shouldn't see any problems with functionality - I know a number of servers running in production with Postgres, one of them with thousands of users. If you want to migrate to SQLite all the same, you can use our data migrator: https://prosody.im/doc/migrator I have been working a lot on LuaDBI recently, but I chose to start with cleaning up the MySQL driver (now done I believe), Postgres is next on my list.

    Changes
    • owner MattJ
    • tags Milestone-0.10
  5. otte.mm on

    This issue rendered my server unusable, nobody was able to login. Not a huge impact at 10 users, but annoying. I've migrated the data from postgres to sqlite, now it seems that everyting is running fine again. I'll try to find some hints for this problem within the logs, at first my only concern was to get it running again.

  6. otte.mm on

    Weird... Strange thing: prosody yells that it can't connect to the database, but postgresql is running and notes that the tabel 'prosody' already exists. Here the log snippets: (i've changed my domain name) prosody: Aug 23 23:19:57 iridium.asdf prosody[476]: xmpp.asdf:storage_sql: Database connection failed: Failed to connect to database: no connection to the server Aug 23 23:19:57 iridium.asdf prosody[476]: modulemanager: Error initializing module 'storage_sql' on 'xmpp.asdf': /usr/lib/prosody/modules/mod_storage_sql.lua:172: Failed to connect to database: no connection to the server stack traceback: /usr/lib/prosody/core/modulemanager.lua:29: in function </usr/lib/prosody/core/modulemanager.lua:29> [C]: in function 'assert' /usr/lib/prosody/modules/mod_storage_sql.lua:172: in main chunk (tail call): ? [C]: in function 'xpcall' /usr/lib/prosody/core/modulemanager.lua:29: in function 'pcall' /usr/lib/prosody/core/modulemanager.lua:171: in function 'do_load_module' /usr/lib/prosody/core/modulemanager.lua:249: in function 'load' /usr/lib/prosody/core/storagemanager.lua:54: in function 'load_driver' /usr/lib/prosody/core/storagemanager.lua:74: in function 'get_driver' /usr/lib/prosody/core/storagemanager.lua:84: in function </usr/lib/prosody/core/storagemanager.lua:83> ... /usr/lib/prosody/core/modulemanager.lua:171: in function 'do_load_module' /usr/lib/prosody/core/modulemanager.lua:249: in function 'load' /usr/lib/prosody/core/usermanager.lua:67: in function '?' /usr/lib/prosody/util/events.lua:67: in function 'fire_event' /usr/lib/prosody/core/hostmanager.lua:98: in function 'activate' /usr/lib/prosody/core/hostmanager.lua:44: in function '?' /usr/lib/prosody/util/events.lua:67: in function 'fire_event' /usr/lib/prosody/../../bin/prosody:334: in function 'prepare_to_start' /usr/lib/prosody/../../bin/prosody:399: in main chunk [C]: ? postgres: Aug 23 23:19:55 iridium.asdf systemd[1]: Starting PostgreSQL database server... [...] Aug 23 23:19:58 iridium.asdf postgres[438]: WARNUNG: eine Transaktion ist bereits begonnen Aug 23 23:19:58 iridium.asdf postgres[438]: FEHLER: Relation „prosody“ existiert bereits Aug 23 23:19:58 iridium.asdf postgres[438]: ANWEISUNG: CREATE TABLE "prosody" ("host" TEXT, "user" TEXT, "store" TEXT, "key" TEXT, "type" TEXT, "value" TEXT); I have no explanation for this behaviour. :( ['edit': oops, forgot a domainname once...]

  7. MattJ on

    Ah ok, thanks for the extra info. I think your issue is unrelated to the original reporter's here. Did your problem happen after a server restart? On some systems Prosody comes up before the database, fails to connect and doesn't retry - this is a known issue #416 (and a tricky one... we sometimes need the DB to be available at startup!).

  8. MattJ on

    I think this falls under #688, which we are aiming to fix for 0.10.

  9. apollo13 on

    @MattJ IDLE in transaction can prevent DDL changes and VACUUM, so in general not something you want to see on a postgres server, even if it does not immediately seem to impact functionality.

  10. MattJ on

    Changes
    • tags Status-Accepted

New comment