PostgreSQL `duplicate key value violates unique constraint`

[cvandesande]

Steps to reproduce

1. Use Postgres 11.5
2. Upgrade from 17.0.3 to 18.0.1
3. See DB Error in the Postgres log.

Expected behaviour

Ideally no errors in the Postgres log

Actual behaviour

Same error repeating over and over

2020-02-16 00:07:11.024 UTC [80736] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2020-02-16 00:12:21.597 UTC [80823] ERROR:  duplicate key value violates unique constraint "fce_fileid_idx"
2020-02-16 00:12:21.597 UTC [80823] DETAIL:  Key (fileid)=(1239344) already exists.
2020-02-16 00:12:21.597 UTC [80823] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2020-02-16 00:17:29.837 UTC [80903] ERROR:  duplicate key value violates unique constraint "fce_fileid_idx"
2020-02-16 00:17:29.837 UTC [80903] DETAIL:  Key (fileid)=(1239344) already exists.
2020-02-16 00:17:29.837 UTC [80903] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

Server configuration

Operating system: Custom Dockerfile

Web server: Nginx Docker mainline

Database: Postgres 11-alpine Docker image (11.5 currently)

PHP version: 7.3.14

Nextcloud version: 18.0.1

Updated from an older Nextcloud/ownCloud or fresh install: Updated from 17.0.3

Where did you install Nextcloud from: Download/extract bz2 in a custom Dockerfile

Signing status:

No errors have been found.

List of activated apps:

App list

Enabled:
  - accessibility: 1.4.0
  - activity: 2.11.0
  - admin_audit: 1.8.0
  - bruteforcesettings: 1.5.0
  - calendar: 2.0.1
  - cloud_federation_api: 1.1.0
  - comments: 1.8.0
  - contacts: 3.1.8
  - dav: 1.14.0
  - federatedfilesharing: 1.8.0
  - federation: 1.8.0
  - files: 1.13.1
  - files_sharing: 1.10.1
  - files_trashbin: 1.8.0
  - files_versions: 1.11.0
  - files_videoplayer: 1.7.0
  - firstrunwizard: 2.7.0
  - logreader: 2.3.0
  - lookup_server_connector: 1.6.0
  - nextcloud_announcements: 1.7.0
  - notes: 3.1.5
  - notifications: 2.6.0
  - oauth2: 1.6.0
  - password_policy: 1.8.0
  - photos: 1.0.0
  - privacy: 1.2.0
  - provisioning_api: 1.8.0
  - rainloop: 6.1.0
  - recommendations: 0.6.0
  - serverinfo: 1.8.0
  - settings: 1.0.0
  - sharebymail: 1.8.0
  - spreed: 8.0.4
  - support: 1.1.0
  - survey_client: 1.6.0
  - systemtags: 1.8.0
  - text: 2.0.0
  - theming: 1.9.0
  - twofactor_backupcodes: 1.7.0
  - twofactor_nextcloud_notification: 2.2.0
  - twofactor_totp: 4.1.2
  - twofactor_u2f: 5.0.2
  - updatenotification: 1.8.0
  - viewer: 1.2.0
  - workflowengine: 2.0.0
Disabled:
  - encryption
  - files_external
  - files_pdfviewer
  - files_rightclick
  - mail
  - user_ldap

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "nextcloud.opendmz.com"
        ],
        "apps_paths": [
            {
                "path": "\/usr\/share\/nginx\/html\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/usr\/share\/nginx\/html\/nextcloud\/apps2",
                "url": "\/apps2",
                "writable": true
            }
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "18.0.1.3",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "forwarded_for_headers": [
            "HTTP_X_FORWARDED",
            "HTTP_FORWARDED_FOR"
        ],
        "overwriteprotocol": "https",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "installed": true,
        "mail_smtpmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "loglevel": 0,
        "maintenance": false,
        "secret": "***REMOVED SENSITIVE VALUE***",
        "filesystem_check_changes": 1,
        "filelocking.enabled": "true",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0,
            "dbindex": 0
        },
        "trashbin_retention_obligation": "auto",
        "overwrite.cli.url": "https:\/\/nextcloud.opendmz.com",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpsecure": "ssl",
        "theme": ""
    }
}

Are you using external storage, if yes which one: NFS

Are you using encryption: yes ZFS encrypted volume

Are you using an external user-backend, if yes which one: no

Client configuration

Browser: Firefox/Any

Operating system: N/A Or Any

Logs

Web server error log

Web server error log

No errors

Nextcloud log (data/nextcloud.log)

Nextcloud log

No recent errors

[s-h-a-r-d]

Also started getting that error recently. Not sure from which version. Does anyone know for sure how far back I need to go in version to not have this issue?

[kesselb]

Similar to #6343

Just a different table. I see that issue more with pgsql than Nextcloud. We have code in place to handle the duplicate key situation. The reason we try to insert a record without checking if a record already exists is performance.

Some versions back a method to insert records has been added. That works by using a special command only available for pqsql and default sql for others database.

Feel free to migrate the places step by step. Patches are always welcome.

[cvandesande]

Not sure how far back to go, or what special command to use. You submitted a patch for the oc_credentials table, is there something similar than can be for this?

[lukasjuhrich]

The reason we try to insert a record without checking if a record already exists is performance.

@kesselb Is that based on any measurement? Having two trivial SQL queries instead of one doesn't look like something performance critical to my (untrained) eyes.

[kesselb]

@lukasjuhrich no. Each RDBMS has their own way to implement upserts. Upsert for PostgreSQL, Insert Ignore for MySQL / MariaDB. The database abstraction layer used by Nextcloud is Doctrine and does not support upsert or insert ignore. To use that features we have to implement that in Nextcloud. That's actually something I don't want to do. It just adds more trouble (see the issues with the upsert implementation we already had).

A long known workaround for that issue is try to update and insert on failure. For some reason PostgreSQL logs those error messages since newer versions. We may workaround that limitation with a select upfront but you still have the timeframe between the select and insert/update for another process / request to create that record. You still need everything like before plus the additional select query.

Probably there are better way to workaround that issues nowdays. Everyone is invited to submit patches and propose better ways to handle such cases. Likely there are issues with the database design / schema. That could be something to start with.

From my point of view that strict duplicate key value handling (without a option to turn it off) is something that makes PostgreSQL a hard choice for PHP based application because the level of concurrency that is required to not trigger that warnings is hard to achieve.

I don't work for Nextcloud GmbH and contribute to Nextcloud as home user. If you need that fixed for your professional setup you may contact Nextcloud GmbH.

[J0WI]

See also #13721