Postgres "idle in transaction"

[GoogleCodeExporter]

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"

Original issue reported on code.google.com by ryperven...@gmail.com on 4 Jul 2013 at 12:01

[GoogleCodeExporter]

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

Original comment by ryperven...@gmail.com on 4 Jul 2013 at 12:23

[GoogleCodeExporter]

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...

Original comment by otte...@googlemail.com on 24 Aug 2014 at 9:13

[GoogleCodeExporter]

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.

Original comment by MWild1 on 24 Aug 2014 at 9:21

• Added labels: Milestone-0.10

[GoogleCodeExporter]

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.

Original comment by otte...@googlemail.com on 24 Aug 2014 at 9:48

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

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...]

Original comment by otte...@googlemail.com on 24 Aug 2014 at 10:07

[GoogleCodeExporter]

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!).

Original comment by MWild1 on 24 Aug 2014 at 1:47