Postgresql getInsertId not working

[LEDfan]

When testing the Chat app on postgresql I receive the following error:

 An exception occurred while executing 'SELECT lastval()': SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session

This error is probably caused on the following line:

core/lib/private/db/adapterpgsql.php

Line 14 in 5ade595

return $this->conn->fetchColumn('SELECT lastval()');

And called by: https://github.com/owncloud/core/blob/master/lib/public/appframework/db/mapper.php#L118
This is called by the insert method on the mapper object here: https://github.com/owncloud/chat/blob/master/lib/och/commands/greet.php#L30

The records are inserted:

owncloud=> SELECT * FROM oc_chat_och_users_online;
 user  |            session_id            | last_online 
-------+----------------------------------+-------------
 admin | 82285ca4c28d1d2a734dfe777a86e0f8 |  1406807445
 admin | f569fed24b53a0c05ac36b9cc205cd66 |  1406807510
 admin | dff16e6977c53c97b56e56f700f403cc |  1406807559
 admin | 95d95bda399467867e98ee75e7c37dfa |  1406808454
(4 rows)

Is this an error in oc/core, or am I doing something wrong?

cc @Raydiation @fossxplorer

[DeepDiver1975]

We have seen this in the past during phpunit test execution.
This happend in cases where the database connection was closed and reopened.

[LEDfan]

I read about the unit tests some time ago. Was the problem solved?
Can I prevent the connection to be closed and reopened?

[DeepDiver1975]

Was the problem solved?

yes - but that was in the database schema migrtion code - I doubt that this i the case here as well.

What are the steps to reproduce this? Maybe I can help debugging

[LEDfan]

After opening index.php without loggin in, oC gives me the warning that I should use Postgresql >= 9.3. On the server 8.1 is running, can this be the problem?

To reproduce, you only have to load the chat app with Postgresql. Mysql etc is working fine.

[fossxplorer]

@LEDfan FYI, i use Postgres 9.3.4 and get that exception.

[LEDfan]

@fossxplorer okay thanks. Than this isn't a problem of 8.1.

[LEDfan]

After enabling debug mode. I get the following stack trace:

An exception occurred while executing 'SELECT lastval()': SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session

#0 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(652): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(PDOException), 'SELECT lastval(...', Array)
#1 /var/www/owncloud/lib/private/db/connection.php(107): Doctrine\DBAL\Connection->executeQuery('SELECT lastval(...', Array, Array, NULL)
#2 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(408): OC\DB\Connection->executeQuery('SELECT lastval(...', Array)
#3 /var/www/owncloud/lib/private/db/adapterpgsql.php(14): Doctrine\DBAL\Connection->fetchColumn('SELECT lastval(...')
#4 /var/www/owncloud/lib/private/db/connection.php(145): OC\DB\AdapterPgSql->lastInsertId('oc_chat_och_use...')
#5 /var/www/owncloud/lib/private/db.php(255): OC\DB\Connection->lastInsertId('*PREFIX*chat_oc...')
#6 /var/www/owncloud/lib/public/db.php(82): OC_DB::insertid('*PREFIX*chat_oc...')
#7 /var/www/owncloud/lib/private/appframework/db/db.php(53): OCP\DB::insertid('*PREFIX*chat_oc...')
#8 /var/www/owncloud/lib/public/appframework/db/mapper.php(118): OC\AppFramework\Db\Db->getInsertId('*PREFIX*chat_oc...')
#9 /var/www/owncloud/apps/chat/lib/och/commands/greet.php(30): OCP\AppFramework\Db\Mapper->insert(Object(OCA\Chat\OCH\Db\UserOnline))
#10 /var/www/owncloud/apps/chat/controller/appcontroller.php(57): OCA\Chat\OCH\Commands\Greet->execute()
#11 [internal function]: OCA\Chat\Controller\AppController->index()
#12 /var/www/owncloud/lib/private/appframework/http/dispatcher.php(155): call_user_func_array(Array, Array)
#13 /var/www/owncloud/lib/private/appframework/http/dispatcher.php(84): OC\AppFramework\Http\Dispatcher->executeController(Object(OCA\Chat\Controller\AppController), 'index')
#14 /var/www/owncloud/lib/private/appframework/app.php(57): OC\AppFramework\Http\Dispatcher->dispatch(Object(OCA\Chat\Controller\AppController), 'index')
#15 /var/www/owncloud/lib/private/appframework/routing/routeactionhandler.php(44): OC\AppFramework\App::main('AppController', 'index', Object(OC\AppFramework\DependencyInjection\DIContainer), Array)
#16 [internal function]: OC\AppFramework\routing\RouteActionHandler->__invoke(Array)
#17 /var/www/owncloud/lib/private/route/router.php(227): call_user_func(Object(OC\AppFramework\routing\RouteActionHandler), Array)
#18 /var/www/owncloud/lib/base.php(714): OC\Route\Router->match('/apps/chat/')
#19 /var/www/owncloud/index.php(28): OC::handleRequest()
#20 {main}

If I dump the value of $this->_isConnected on line 628 of Doctrine\DBAL\Connection when the query SELECT lastval() is executed, it's true.
So it looks like the connection is stile alive before the query is executed.
I also added the line echo "connecting"; on line 344 of the same line. Now I let the script die after the executeQuery function is called for the SELECT lastval() query. The result is just one connecting. I guess this proves the connecting isn't reset? Or am I missing something?

[LEDfan]

@bartv2 you wrote the adapter, maybe you know what's wrong here?

[DeepDiver1975]

let me debug this ....

[DeepDiver1975]

@LEDfan what do you expect as return? the table chat_och_users_online has no auto increment columns - lastInsertId can simply not be determined. 😉

[LEDfan]

@DeepDiver1975 thanks! So stupid of me.....

[DeepDiver1975]

So stupid of me.....

I'd call it: shit happens! 😉

No worries!