Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

PostgreSQL duplicate key value violates unique constraint #19494

Open
cvandesande opened this issue Feb 16, 2020 · 30 comments · May be fixed by #48564
Open

PostgreSQL duplicate key value violates unique constraint #19494

cvandesande opened this issue Feb 16, 2020 · 30 comments · May be fixed by #48564
Labels
1. to develop Accepted and waiting to be taken care of 26-feedback bug feature: filesystem

Comments

@cvandesande
Copy link

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
@cvandesande cvandesande added 0. Needs triage Pending check for reproducibility or if it fits our roadmap bug labels Feb 16, 2020
@s-h-a-r-d
Copy link

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
Copy link
Contributor

kesselb commented Mar 9, 2020

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
Copy link
Author

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
Copy link

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
Copy link
Contributor

kesselb commented Apr 10, 2020

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

@kesselb kesselb mentioned this issue Apr 10, 2020
Closed
@jokay jokay mentioned this issue Aug 18, 2020
Closed
@J0WI
Copy link
Contributor

J0WI commented Nov 13, 2020

See also #13721

@J0WI J0WI added 1. to develop Accepted and waiting to be taken care of and removed 0. Needs triage Pending check for reproducibility or if it fits our roadmap labels Dec 27, 2020
@J0WI J0WI mentioned this issue Dec 27, 2020
Open
15 tasks
@khumarahn
Copy link

I see this each time I modify a file, still present in nextcloud 22.2.3

Nov 19 23:03:13 hostname postgres[787]: 2021-11-19 22:03:13.225 GMT [787] ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Nov 19 23:03:13 hostname postgres[787]: 2021-11-19 22:03:13.225 GMT [787] DETAIL:  Key (fileid)=(235814) already exists.
Nov 19 23:03:13 hostname postgres[787]: 2021-11-19 22:03:13.225 GMT [787] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

@cartman29
Copy link 

2021-12-07 09:10:02.564 CET [39333] XXXXXX@YYYYYY ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « fs_storage_path_hash »
2021-12-07 09:10:02.564 CET [39333] XXXXXX@YYYYYY DÉTAIL:  La clé « (storage, path_hash)=(3, 13c337fec*************1cf86af2c) » existe déjà.
2021-12-07 09:10:02.564 CET [39333] XXXXXX@YYYYYY INSTRUCTION :  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

Also another duplocate key value...

@cartman29 cartman29 mentioned this issue Dec 10, 2021
Closed
@enoch85
Copy link
Member

enoch85 commented Jan 5, 2022

Don't know if this helps, but saw this in a log with similar issues:

2022-01-03 07:16:26.430 UTC [122115] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-03 07:16:26.430 UTC [122115] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3374663) already exists.
2022-01-03 07:16:26.430 UTC [122115] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:02:23.024 UTC [179136] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:02:23.024 UTC [179136] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:02:23.024 UTC [179136] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:26:51.568 UTC [179824] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:26:51.568 UTC [179824] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:26:51.568 UTC [179824] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:48:56.322 UTC [180356] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:48:56.322 UTC [180356] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:48:56.322 UTC [180356] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:53:23.192 UTC [180452] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:53:23.192 UTC [180452] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:53:23.192 UTC [180452] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:55:53.929 UTC [180504] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:55:53.929 UTC [180504] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:55:53.929 UTC [180504] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2022-01-05 11:57:50.276 UTC [180711] ncadmin@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2022-01-05 11:57:50.276 UTC [180711] ncadmin@nextcloud_db DETAIL:  Key (fileid)=(3462447) already exists.
2022-01-05 11:57:50.276 UTC [180711] ncadmin@nextcloud_db STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

Problem description from customer:

It appears to be very slow.
Adding a share takes about 30 seconds.

Calling the „settings“ page takes 30-45seconds.

@tennox
Copy link

tennox commented Feb 4, 2022

I found this even in nextcloud CI while googling: 😅
https://drone.nextcloud.com/nextcloud/server/6252/14/3

@hartsberger
Copy link

This also happens with Nextcloud 21.

Nextcloud version: 21.0.9
Postgres version: PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
DETAIL: Key (fileid)=(764531) already exists.
STATEMENT: INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

@J0WI
Copy link
Contributor

J0WI commented Mar 2, 2022

All versions are affected, but <22 is eol.

@urandom2 urandom2 mentioned this issue Apr 17, 2022
Closed
8 tasks
@aknrdureegaesr
Copy link

aknrdureegaesr commented May 4, 2023 •
edited
Loading

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.

The error messages complain about the INSERT violating the unique constraint, not the UPDATE. With the workaround as described, the UPDATE should be logged.

I did not check the code, so this is purely speculative: Is the workaround that is done the other way around? That is: Try the INSERT and do an UPDATE it the INSERT fails? Personally, I would like that order better, as it leaves no time window for a race condition, should two such operations be done in parallel.

If that speculation happens to be true, this means that the error messages as are generated by that workaround are a nuisance, but a harmless one.

@szaimen szaimen added needs info 0. Needs triage Pending check for reproducibility or if it fits our roadmap labels May 22, 2023
@KopfKrieg
Copy link

Still relevant. Current setup includes:

  • Nextcloud 26.0.1 (using Nextcloud's official docker image)
  • PostgreSQL 13.11
2023-05-22 20:55:51.221 UTC [17410] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
2023-05-22 20:55:51.221 UTC [17410] DETAIL:  Key (storage, path_hash)=(1, ecdffe1b137d612fd3982118c65ff3c1) already exists.
2023-05-22 20:55:51.221 UTC [17410] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

@szaimen szaimen changed the title Postgres "duplicate key value" after 18.0.1 upgrade Postgres "duplicate key value" May 22, 2023
@julio-horna
Copy link

Same here, using Nextcloud AIO 26.0.3.

Tons of these:

2023-07-07 02:13:44.097 UTC [192094] ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2023-07-07 02:13:44.097 UTC [192094] DETAIL:  Key (fileid)=(1705435) already exists.
2023-07-07 02:13:44.097 UTC [192094] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2023-07-07 02:13:59.672 UTC [192246] ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2023-07-07 02:13:59.672 UTC [192246] DETAIL:  Key (fileid)=(1696526) already exists.
2023-07-07 02:13:59.672 UTC [192246] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
2023-07-07 02:14:09.029 UTC [192286] ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
2023-07-07 02:14:09.029 UTC [192286] DETAIL:  Key (fileid)=(1705435) already exists.

@julio-horna
Copy link

@szaimen is using ON CONFLICT DO NOTHING a possibility to avoid bloating and performance impacts due to dead tuples?

@joshtrichards joshtrichards changed the title Postgres "duplicate key value" Postgres "duplicate key value violates unique constraint" Sep 15, 2023
@joshtrichards joshtrichards changed the title Postgres "duplicate key value violates unique constraint" PostgreSQL "duplicate key value violates unique constraint" Sep 15, 2023
@joshtrichards joshtrichards changed the title PostgreSQL "duplicate key value violates unique constraint" PostgreSQL duplicate key value violates unique constraint Sep 15, 2023
@joshtrichards joshtrichards added 1. to develop Accepted and waiting to be taken care of and removed 0. Needs triage Pending check for reproducibility or if it fits our roadmap labels Sep 15, 2023
@Irdiism
Copy link

Irdiism commented Oct 17, 2023

Just encountered the same problem on 27.0.2.

@tobiasKaminsky tobiasKaminsky mentioned this issue Nov 7, 2023
Open
8 tasks
@nodiscc
Copy link

nodiscc commented Dec 6, 2023

Still present on 27.1.4 (with relatively high frequency - logs below for a single-user instance). This seems to only affect the oc_filecache_extended_pkey constraint on my instances.

/var/log/postgresql/postgresql-15-main.log|2023-12-04 00:01:45.754 CET [386622] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-04 00:01:45.754 CET [386622] nextcloud@nextcloud DETAIL:  Key (fileid)=(75737) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-04 00:01:45.754 CET [386622] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time", "creation_time") VALUES($1, $2, $3)
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2554] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2554] nextcloud@nextcloud DETAIL:  Key (fileid)=(68625) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2554] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2553] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2553] nextcloud@nextcloud DETAIL:  Key (fileid)=(67033) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.581 CET [2553] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.584 CET [2552] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.584 CET [2552] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:57.584 CET [2552] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:59.585 CET [2557] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:59.585 CET [2557] nextcloud@nextcloud DETAIL:  Key (fileid)=(66735) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-04 16:56:59.585 CET [2557] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.295 CET [96196] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.295 CET [96196] nextcloud@nextcloud DETAIL:  Key (fileid)=(67033) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.295 CET [96196] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.669 CET [96195] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.669 CET [96195] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:05:43.669 CET [96195] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.116 CET [96255] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.116 CET [96255] nextcloud@nextcloud DETAIL:  Key (fileid)=(66735) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.116 CET [96255] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.806 CET [96254] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.806 CET [96254] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:13.806 CET [96254] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:26.668 CET [96288] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:26.668 CET [96288] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:26.668 CET [96288] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.223 CET [96305] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.223 CET [96305] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.223 CET [96305] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.273 CET [96306] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.273 CET [96306] nextcloud@nextcloud DETAIL:  Key (fileid)=(66735) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:06:34.273 CET [96306] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.423 CET [96651] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.423 CET [96651] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.423 CET [96651] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.642 CET [96650] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.642 CET [96650] nextcloud@nextcloud DETAIL:  Key (fileid)=(68625) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-05 16:07:43.642 CET [96650] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58433] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58433] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58433] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58434] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58434] nextcloud@nextcloud DETAIL:  Key (fileid)=(67033) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:39.892 CET [58434] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:55.751 CET [58608] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:55.751 CET [58608] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:55.751 CET [58608] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:56.794 CET [58609] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:56.794 CET [58609] nextcloud@nextcloud DETAIL:  Key (fileid)=(66735) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:05:56.794 CET [58609] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.641 CET [59277] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.641 CET [59277] nextcloud@nextcloud DETAIL:  Key (fileid)=(73335) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.641 CET [59277] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.832 CET [59278] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.832 CET [59278] nextcloud@nextcloud DETAIL:  Key (fileid)=(68625) already exists.
/var/log/postgresql/postgresql-15-main.log|2023-12-06 16:07:16.832 CET [59278] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)

@Railsimulatornet
Copy link

Railsimulatornet commented Feb 12, 2024 •
edited
Loading

Same Problem Nextcloud Hub 7 (28.0.2)

2024-02-12 13:17:47.647 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:47.647 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290615, 1647745758) already exists. 2024-02-12 13:17:47.647 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:48.141 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:48.141 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290616, 1647745759) already exists. 2024-02-12 13:17:48.141 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:48.623 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:48.623 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290617, 1647745759) already exists. 2024-02-12 13:17:48.623 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:49.084 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:49.084 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290618, 1647745760) already exists. 2024-02-12 13:17:49.084 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:49.496 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:49.496 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290619, 1647745761) already exists. 2024-02-12 13:17:49.496 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:49.970 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:49.970 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290620, 1647745759) already exists. 2024-02-12 13:17:49.970 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:50.424 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:50.424 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290621, 1647745761) already exists. 2024-02-12 13:17:50.424 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:50.915 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:50.915 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290622, 1647745760) already exists. 2024-02-12 13:17:50.915 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:51.366 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:51.366 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290623, 1647745762) already exists. 2024-02-12 13:17:51.366 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:51.821 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:51.821 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290624, 1647745762) already exists. 2024-02-12 13:17:51.821 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:52.327 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:52.327 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290625, 1647745763) already exists. 2024-02-12 13:17:52.327 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:52.769 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:52.769 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290626, 1647745762) already exists. 2024-02-12 13:17:52.769 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:53.262 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:53.262 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290627, 1647745763) already exists. 2024-02-12 13:17:53.262 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:53.742 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:53.742 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290628, 1647745761) already exists. 2024-02-12 13:17:53.742 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:54.201 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:54.201 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290629, 1647745765) already exists. 2024-02-12 13:17:54.201 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2 2024-02-12 13:17:54.653 UTC [1300788] nextcloud_db_user@nextcloud_db ERROR: duplicate key value violates unique constraint "files_versions_uniq_index" 2024-02-12 13:17:54.653 UTC [1300788] nextcloud_db_user@nextcloud_db DETAIL: Key (file_id, "timestamp")=(6290630, 1647745765) already exists. 2024-02-12 13:17:54.653 UTC [1300788] nextcloud_db_user@nextcloud_db STATEMENT: UPDATE "oc_files_versions" SET "timestamp" = $1 WHERE "id" = $2

@ca5ua1
Copy link

ca5ua1 commented Feb 22, 2024

Still relevant. Single user instance. Almost can't upload files without constant 504 gateway errors. Is there any fix?

Feb 22 10:21:12 NIX-Servicer-nextcloud postgres[366493]: [366493] ERROR:  duplicate key value violates unique constraint "oc_filecache_extended_pkey"
Feb 22 10:21:12 NIX-Servicer-nextcloud postgres[366493]: [366493] DETAIL:  Key (fileid)=(768929) already exists.
Feb 22 10:21:12 NIX-Servicer-nextcloud postgres[366493]: [366493] STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time", "creation_time") VALUES($1, $2, $3)

@J0WI
Copy link
Contributor

J0WI commented Feb 24, 2024

An approach similar to 4361019 is required here and here to fix this.

@maximelehericy
Copy link

this happens on other tables as well:

62398 ERROR: duplicate key value violates unique constraint "oc_filecache_extended_pkey"
4420 ERROR: duplicate key value violates unique constraint "oc_file_metadata_pkey"
354 ERROR: duplicate key value violates unique constraint "fs_storage_path_hash"
2 ERROR: duplicate key value violates unique constraint "oc_text_documents_pkey"
2 ERROR: duplicate key value violates unique constraint "oc_npushhash_uid"

@ca5ua1

This comment was marked as off-topic.

Sign in to view
@kesselb
Copy link
Contributor

kesselb commented Sep 23, 2024 •
edited
Loading

To summarize the situation:

We added the table filecache_extended to store metadata_etag, creation_time and upload_time1. The information in filecache_extended is optional, and it's okay for filecache and filecache_extended to have a different row count.

If the clients pass creation_time, then there's an additional query to store the creation_time in filecache_extended. The approach is to try to insert a new row and if that fails with a unique constraint violation update the existing one2.

Possible solutions:

Curl request to trigger such a warning on pqsql:

curl -X PUT --location "https://admin:[email protected]/remote.php/dav/files/admin/hello.txt" \
    -H "Content-Type: text/plain; charset="utf-8"" \
    -H "Cookie: XDEBUG_SESSION=PHPSTORM" \
    -H "X-OC-CTime: 1632391281" \
    -d 'Hello World!'

Run it twice, the important piece is the X-OC-CTime header to trigger the insert/update for filecache_extended.

Footnotes

  1. https://github.com/nextcloud/server/pull/17765 ↩

  2. https://github.com/nextcloud/server/blob/7ddad46137c17e3c272adbcf02ca29d7e8b2d6bd/lib/private/Files/Cache/Cache.php#L361-L391 ↩

@mduller
Copy link

mduller commented Sep 23, 2024

Would it be possible to use the database backends' native capability to deal with this situation in a single statement instead of stringing it together at the application level with separate INSERT & UPDATE statements?

@J0WI
Copy link
Contributor

J0WI commented Sep 24, 2024

Would it be possible to use the database backends' native capability to deal with this situation in a single statement instead of stringing it together at the application level with separate INSERT & UPDATE statements?

Sure, but unfortunately the Doctrine library does not support it. There are some extensions to it like in https://github.com/nextcloud/server/blob/master/lib/private/DB/AdapterPgSql.php#L26 but it need to adopted for this case.

@mduller
Copy link

mduller commented Sep 25, 2024

Thanks for the explanation @J0WI!

@urfin73
Copy link

urfin73 commented Sep 26, 2024

Hello!
Tell me, is there any solution? There are numerous errors in the postgres container log:

STATEMENT:  INSERT INTO "oc_filecache_extended" ("fileid", "upload_time") VALUES($1, $2)
ERROR:  duplicate key value violates unique constraint "files_versions_uniq_index"
DETAIL:  Key (file_id, "timestamp")=(542859, 1727077224) already exists.

The table pg_stat_activity constantly accumulates requests of the type:

UPDATE "oc_filecache" SET "mtime" = $1, "storage_mtime" = $2 WHERE ("fileid" = $3) AND ("storage" = $4) AND ((("mtime" <> $5) OR ("mtime" IS NULL)) OR (("storage_mtime" <> $6) OR ("storage_mtime" IS NULL)))

Their number can be more than 130
As a result, nextcloud becomes unavailable.
Docker instalation Nextcloud Hub 9 (30.0.0)

@kesselb kesselb linked a pull request Oct 7, 2024 that will close this issue
Open
4 tasks
@kesselb
Copy link
Contributor

kesselb commented Oct 7, 2024

I've prepared a pull request last week for the filecache extended table: #48564

We can't merge the patch yet because the CI is failing. The issue is that our insertIgnoreConflict implementation doesn't support sharding. Sharding, as you know, is something we're working on to distribute the filecache table across multiple database instances.

Here are the alternatives:

  1. Flip insert and update: While this is an option, the update clause currently includes known data (e.g., update filecache_extended set creation_time = 20000 where fileid = 1000 and (creation_time <> 20000 or creation_time is null)). This avoids unnecessary writes, which is useful for clustered setups. However, flipping them would force us to drop that optimization, as we wouldn't be able to distinguish between no existing row and a row with the same data.
  2. Select upfront: Doing a select before insert could reduce log errors. There is a risk of race conditions, but that might be acceptable. If the select returns 0, and we attempt to insert but hit a unique constraint, it's likely someone else inserted the row first. We can handle this by catching the exception and continuing.
  3. Adjust insertIgnoreConflict: We could either modify insertIgnoreConflict or create a new method (e.g., insertIgnoreConflictWithSharding) to work with sharding.
  4. Migrate filecache_extended to files_metadata: I didn't find significant usage of creation_time or upload_time outside of DAV responses. Moving this data to files_metadata might be a cleaner solution.
  5. Do nothing: While the log errors are annoying and could be frequent, they're handled in our application code by updating the matching row. I believe issues like Nextcloud being unresponsive, as mentioned in this thread, are unrelated. If needed, your log management system should be able to filter these errors.

I'll ping the files team to discuss the best way forward.

@kesselb kesselb mentioned this issue Oct 7, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
1. to develop Accepted and waiting to be taken care of 26-feedback bug feature: filesystem
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix: use insert ignore for filecache_extended nextcloud/server