PostgreSQL: violation of constraints

[pmattern]

Using PostgreSQL some database constraints are by default violated.

From the second time users log in onwards there are messages

ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(nextcloud_admin, login, lastLogin) already exists.
STATEMENT:  INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)

and

ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(nextcloud_admin, core, timezone) already exists.
STATEMENT:  INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)

The first time a user is browsing the sample "Photos" in section "All files" there's

ERROR:  duplicate key value violates unique constraint "mimetype_id_index"
DETAIL:  Key (mimetype)=(image/png) already exists.
STATEMENT:  INSERT INTO "oc_mimetypes" ("mimetype") VALUES($1)

Seen running 9.0.52 on Arch Linux (PostgreSQL 9.5.3, PHP 7.0.8, Apache 2.4.23 using the PHP module).

[nickvergessen]

What's the transaction isolation level you are using?

[pmattern]

As far as the PostgreSQL server is concerned the default "read committed" was not changed.
(Don't know whether the PostgreSQL backend of Nextcloud is modifying the value.)

[nickvergessen]

Read commited is what we need, so that seems to be fine.

[Alphakilo]

I'm getting this behaviour on Nextcloud 10.0 with PostgreSQL 9.5.4:

maint@files:~# grep "duplicate key value violates unique constraint" /var/log/postgresql/postgresql-9.5-main.log | cut -d " " -f 6- | sort | uniq
ERROR:  duplicate key value violates unique constraint "lock_key_index"
ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"

Over at owncloud @nickvergessen seems to have found the underlying issue already (correct me if I'm wrong here):
owncloud/core#23213

There is a workaround by @mobstef here to silence the logoutput:
owncloud/core#23213 (comment)

Although it needs to be modified for the oc_preferences_pkey relation.
Postgres 9.5 has implemented UPSERT that would allow to UPDATE (if the tulple does not exist) or INSERT (if it does not exist yet) at database level.

Reference:
https://wiki.postgresql.org/wiki/UPSERT
https://www.postgresql.org/docs/9.5/static/sql-insert.html

Then again UPSERT is only avaible on Postgres 9.5 and above, but there are equivalents for MySQL.

[mp1409]

I had this error for both oc_preferences and oc_credentials with several log entries like this per second :

ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(XXX, login, lastLogin) already exists.

ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
 DETAIL:  Key ("user", identifier)=(XXX, password::logincredentials/credentials) already exists.

Based upon owncloud/core#23213 (comment) , the following rules silence the log messages:

CREATE RULE oc_preferences_ignore_duplicate_inserts AS 
ON INSERT TO oc_preferences WHERE  (EXISTS 
(SELECT 1 FROM oc_preferences WHERE oc_preferences.userid = NEW.userid and oc_preferences.appid = NEW.appid and oc_preferences.configkey = NEW.configkey))
DO INSTEAD NOTHING;

CREATE RULE oc_credentials_ignore_duplicate_inserts AS 
ON INSERT TO oc_credentials WHERE (EXISTS 
(SELECT 1 FROM oc_credentials WHERE oc_credentials.user = NEW.user and oc_credentials.identifier = NEW.identifier))
DO INSTEAD NOTHING;

Just in case that helps someone :-)

[andreas-p]

The rule mentioned above is incomplete, since it doesn't handle the update case. Simply fixed:
(...) DO INSTEAD
UPDATE oc_preferences SET configvalue=NEW.configvalue
WHERE oc_preferences.userid = NEW.userid and oc_preferences.appid = NEW.appid and oc_preferences.configkey = NEW.configkey

But this is just a poor patch. With user_ldap active, every access will try to insert and then (after ConstraintViolation) update displayName, uid and lastFeatureRefresh, resulting in 6 database roundtrips. This appears very inefficient to me. In addition, PostgreSQL will have the preferences table bloated since each update (replacing the same value over and over again) will create an additional row. On my test system, preferences has 106 rows but >74000 estimated rows (including outdated rows) consuming 5MB, indicating unhealtly access patterns.
Situation can improve vastly by checking if the value has changed at all and skipping the insert/update if not.

[MorrisJobke]

Some of this will hopefully be gone with #2147

[jcgruenhage]

is there anyone working on this? Although everything seems to work, running occ files:scan --all throws an exception, trying to update displayName. It is somewhat weird though, that the exception is about the user 3aee88e4-3657-1036-85e2-8bb479b1dffc, but occurs after scanning his files, during scanning the files of the user 3ca7f660-3b12-1036-9932-a142a160eb37.

Extract from the output of the occ command:

Starting scan for user 4 out of 10 (3aee88e4-3657-1036-85e2-8bb479b1dffc)
Starting scan for user 5 out of 10 (3ca7f660-3b12-1036-9932-a142a160eb37)
Exception while scanning: An exception occurred while executing 'UPDATE "oc_preferences" SET "configvalue" = ? WHERE ("userid" = ?) AND ("appid" = ?) AND ("configkey" = ?)' with params ["jcgruenhage", "3aee88e4-3657-1036-85e2-8bb479b1dffc", "user_ldap", "displayName"]:

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block
#0 /nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(116): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
#1 /nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(996): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'UPDATE "oc_pref...', Array)
#2 /nextcloud/lib/private/DB/Connection.php(210): Doctrine\DBAL\Connection->executeUpdate('UPDATE "oc_pref...', Array, Array)
#3 /nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(208): OC\DB\Connection->executeUpdate('UPDATE "oc_pref...', Array, Array)
#4 /nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(142): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /nextcloud/lib/private/DB/Connection.php(299): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /nextcloud/lib/private/AllConfig.php(230): OC\DB\Connection->setValues('preferences', Array, Array, Array)
#7 /nextcloud/apps/user_ldap/lib/User/User.php(374): OC\AllConfig->setUserValue('3aee88e4-3657-1...', 'user_ldap', 'displayName', 'jcgruenhage')
#8 /nextcloud/apps/user_ldap/lib/User/User.php(389): OCA\User_LDAP\User\User->store('displayName', 'jcgruenhage')
#9 /nextcloud/apps/user_ldap/lib/User_LDAP.php(399): OCA\User_LDAP\User\User->composeAndStoreDisplayName('jcgruenhage', '')
#10 /nextcloud/lib/private/User/User.php(116): OCA\User_LDAP\User_LDAP->getDisplayName('3aee88e4-3657-1...')
#11 /nextcloud/lib/private/legacy/user.php(348): OC\User\User->getDisplayName()
#12 /nextcloud/apps/files_sharing/lib/Cache.php(94): OC_User::getDisplayName('3aee88e4-3657-1...')
#13 /nextcloud/lib/private/Files/Cache/Wrapper/CacheWrapper.php(67): OCA\Files_Sharing\Cache->formatCacheEntry(Object(OC\Files\Cache\CacheEntry))
#14 /nextcloud/lib/private/Files/Cache/Wrapper/CacheJail.php(101): OC\Files\Cache\Wrapper\CacheWrapper->get('files/Studium')
#15 /nextcloud/lib/private/Files/Cache/Scanner.php(177): OC\Files\Cache\Wrapper\CacheJail->get('files/Studium')
#16 /nextcloud/apps/files_sharing/lib/Scanner.php(73): OC\Files\Cache\Scanner->scanFile('', 3, 30287, NULL, true)
#17 /nextcloud/lib/private/Files/Cache/Scanner.php(310): OCA\Files_Sharing\Scanner->scanFile('', 3, -1, NULL, true)
#18 /nextcloud/lib/private/Files/Utils/Scanner.php(196): OC\Files\Cache\Scanner->scan('', true, 3)
#19 /nextcloud/apps/files/lib/Command/Scan.php(159): OC\Files\Utils\Scanner->scan('/3ca7f660-3b12-...')
#20 /nextcloud/apps/files/lib/Command/Scan.php(227): OCA\Files\Command\Scan->scanFiles('3ca7f660-3b12-1...', '/3ca7f660-3b12-...', false, Object(Symfony\Component\Console\Output\ConsoleOutput), false)
#21 /nextcloud/3rdparty/symfony/console/Command/Command.php(259): OCA\Files\Command\Scan->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 /nextcloud/core/Command/Base.php(159): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#23 /nextcloud/3rdparty/symfony/console/Application.php(844): OC\Core\Command\Base->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#24 /nextcloud/3rdparty/symfony/console/Application.php(192): Symfony\Component\Console\Application->doRunCommand(Object(OCA\Files\Command\Scan), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#25 /nextcloud/3rdparty/symfony/console/Application.php(123): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#26 /nextcloud/lib/private/Console/Application.php(146): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#27 /nextcloud/console.php(90): OC\Console\Application->run()
#28 /nextcloud/occ(11): require_once('/nextcloud/cons...')
#29 {main}
Starting scan for user 6 out of 10 (44c1f93c-4525-1036-8e99-392fd7bf1de1)

It goes through with users 6 through 10 without problems after that though and I don't miss any files online, so it still seems to work.

Also, every few seconds, Nextcloud tries to insert homePath, which already exists.

ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(3aee88e4-3657-1036-85e2-8bb479b1dffc, user_ldap, homePath) already exists.
STATEMENT:  INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)

I observed that every one of these issues appears for the same user though.

So, if there is anyone working on this, do you need any logs? I don't know php at all so I can not help with that unfortunately.

[MorrisJobke]

cc @icewind1991 @rullzer

[pmattern]

Running b5990b7 on the same system except the updates which took place in the meantime (Apache 2.4.25, PHP 7.1.1, PostgreSQL 9.6.1) I cannot see the initial problem any longer.
Didn't try to bisect all commits possibly involved by now.

Also, I cannot confirm the trouble caused by running sudo -u http php occ files:scan --all as described above either which is working flawlessly here.

Running Nextcloud in virtual machines for testing purposes only so far. So in particular new database for each test and only few users and files, which I guess may have some impact e. g. on the findings with occ.

[enoch85]

Is this supposed to be fixed? Just upgraded my PostgreSQL to 9.6 running 12.0.0 and got this:

2017-08-01 00:04:21 CEST [19998-1] ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-08-01 00:04:21 CEST [19998-2] DETAIL:  Key ("user", identifier)=(kelly, password::logincredentials/credentials) already exists.
2017-08-01 00:04:21 CEST [19998-3] STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

EDIT

Cleared my Redis cache with FLUSHALL and the error is gone. Sorry for the noise.

EDIT2

Hmm, I forgot to login and logout again, error still appears. :(

[enoch85]

Still fails --^ :(

[volga629]

Hello Everyone,
We getting same errors

nextcloud-postgresql-10.0.4-3.fc27.noarch
nextcloud-10.0.4-3.fc27.noarch
nextcloud-nginx-10.0.4-3.fc27.noarch

DETAIL:  Key ("user", identifier)=(username, password::logincredentials/credentials) already exists.
STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(username, login, lastLogin) already exists.
STATEMENT:  INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)
ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"

[jirutka]

Similar issue: #9305. It seems that Nextcloud has serious problem with concurrency…

[github-k8n]

Any change regarding this? As I am using ldap auth, my postgres logs are full with the error messages
ERROR: duplicate key value violates unique constraint "oc_credentials_pkey"
(basically on each request every 30 seconds when using nextcloud client)

In my opinion the best solution would be to use UPSERT (as mentioned earlier), maybe in an opt-in way to only enable it on systems using postgresql-9.5 or later.

[DanScharon]

please reopen the issue, I'm still seeing this as well (also with LDAP auth)

[github-k8n]

I actually implemented the suggestion from above

CREATE RULE oc_credentials_ignore_duplicate_inserts AS 
ON INSERT TO oc_credentials WHERE (EXISTS 
(SELECT 1 FROM oc_credentials WHERE oc_credentials.user = NEW.user and oc_credentials.identifier = NEW.identifier))
DO INSTEAD NOTHING;

Considering that this only affects INSERT, any UPDATE should still work as expected. (not sure if the current INSERTs are supposed to also update (or if there is a separate update in case the INSERT fails)
So this should be an acceptable workaround for the moment... (still, would be good to fix the underlying issue of trying to do INSERT all the time..)

[reinob]

Just migrated from Mysql to PostgreSQL (version 11) and noticed the exact same error:

2018-12-15 12:16:01.517 CET [13551] nextcloud@nextcloud ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2018-12-15 12:16:01.517 CET [13551] nextcloud@nextcloud DETAIL: Key ("user", identifier)=(reinob, password::logincredentials/credentials) already exists. 2018-12-15 12:16:01.517 CET [13551] nextcloud@nextcloud STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

Happens (at least, maybe also in other situations) everytime when logging in..

[silenius]

same here with PostgreSQL 11, FreeBSD 12, and Nextcloud 15:

Jan  4 15:58:42 dev postgres[3643]: [9-3] 2019-01-04 15:58:42.953 CET [3643] STATEMENT:  INSERT INTO "oc_file_locks" ("key", "lock", "ttl") VALUES($1, $2, $3)
Jan  4 15:58:43 dev postgres[3643]: [10-1] 2019-01-04 15:58:43.021 CET [3643] ERROR:  duplicate key value violates unique constraint "lock_key_index"
Jan  4 15:58:43 dev postgres[3643]: [10-2] 2019-01-04 15:58:43.021 CET [3643] DETAIL:  Key (key)=(files/4c302ab2a54be2e3cee5e4c8590feb70) already exists.

[seonghobae]

same here with PostgreSQL 11, FreeBSD 12, and Nextcloud 15:

Jan  4 15:58:42 dev postgres[3643]: [9-3] 2019-01-04 15:58:42.953 CET [3643] STATEMENT:  INSERT INTO "oc_file_locks" ("key", "lock", "ttl") VALUES($1, $2, $3)
Jan  4 15:58:43 dev postgres[3643]: [10-1] 2019-01-04 15:58:43.021 CET [3643] ERROR:  duplicate key value violates unique constraint "lock_key_index"
Jan  4 15:58:43 dev postgres[3643]: [10-2] 2019-01-04 15:58:43.021 CET [3643] DETAIL:  Key (key)=(files/4c302ab2a54be2e3cee5e4c8590feb70) already exists.

I also experienced on Ubuntu 16.04, Nextcloud 15, PostgreSQL 11.

[MorrisJobke]

Just to be clear: this is actually wanted behavior. The SQL statements we used before where causing deadlocks in certain situations. So we went for the approach of inserting the column and let the DB handle this case with either an exception or an insertion without issues. In case of the exception we catch it in the application and know that the entry was already there. Then we handle it in the application logic. Unfortunately this is not the perfect way to do it, but the most robust one when it comes to handling this for the different DBs that we support.

[reinob]

@MorrisJobke

Good to know. I hope you can add this to the manual (https://docs.nextcloud.com/server/stable/admin_manual/configuration_database/linux_database_configuration.html), so that people installing Nextcloud with PosgreSQL know this in advance. Otherwise it makes sense to think it's a bug in Nextcloud.

[andreas-p]

While having a procedure throw an exception and act accordingly is standard programming technique for today's languages, this is not the case for SQL. It will flood the error log, eventually burying the real errors, so this isn't really administrator friendly and should be avoided.

There's probably no simple rdbms-agnostic way to achieve this behaviour (insert .. on conflict and stored procedures are alternatives to rules for pgsql, none of them portable)

[MorrisJobke]

There's probably no simple rdbms-agnostic way to achieve this behaviour (insert .. on conflict and stored procedures are alternatives to rules for pgsql, none of them portable)

Exactly that was the problem :/ And it also caused some weird corner cases as well that were then even hard to debug because they were super difficult to reproduce.

[r2evans]

@MorrisJobke, I certainly appreciate the problem when trying to support multiple DBMSs, thank you for that insight.

I think @andreas-p hit the nail on the head, though, with:

It will flood the error log, eventually burying the real errors, so this isn't really administrator friendly and should be avoided.

In general, numbing the admin to a big error file will mask true errors and it is in general bad practice (imo) to encourage that behavior.

I'm hoping that if a fix is found that is general-enough for multiple DBMSs, you would consider using it. With a closed issue, though, this is difficult as (1) out of sight, out of mind; and (2) users that are considering working on code for PRs are more likely to look at the "is:open" queue. Does the company maintain an internal list of problems like this that are "known problems", currently "unfixable", but still "on the horizon"? If not, would it not make sense to re-open this issue and label it with something like or just ?

[kesselb]

There are a lot of this postgres issues around here. #12729 is another one and there is already someone working on a potential fix.

[r2evans]

@danielkesselberg thanks for the perspective. Admittedly it's difficult to keep track of the issues, so I haven't seen many of them.

That one you linked to, however, was closed over a month before the PR was first submitted, and even there MorrisJobke is saying the same thing as here: it's on purpose and there is no stated intent to fix or even track the underlying issue. Is there any indication that this direction is going to be accepted? (It's failing the build at the moment ...)

[seonghobae]

@MorrisJobke Unfortunately, That behaviour makes generates too many logs every transaction, approx. 10GB. That's makes disk storage consumption, so I annoying every day to manage the PostgreSQL system.