PostgreSQL `duplicate key value violates unique constraint`
Open
Nobody has claimed this yet.
1. to develop
26-feedback
bug
feature: database
feature: filesystem
- Dominant language
- PHP
- Stars
- 36.9k
- Forks
- 5.2k
- Avg merge
- 2d 3h
- Merged PRs (30d)
- 713
Description
Steps to reproduce
- Use Postgres 11.5
- Upgrade from 17.0.3 to 18.0.1
- 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
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the PostgreSQL 11.5 upgrade from Nextcloud 17.0.3 to 18.0.1 and inspect the reported INSERT into oc_filecache_extended in the PostgreSQL log. No source file or test is named; done means identifying why the duplicate insert repeats and defining a verified fix without further unique-constraint errors.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- php, postgresql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100