server icon indicating copy to clipboard operation
server copied to clipboard
verified publisher icon nextcloud

Postgres "duplicate key value" after 18.0.1 upgrade

Open cvandesande opened this issue 5 years ago • 13 comments

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 avatar Feb 16 '20 00:02 cvandesande

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?

s-h-a-r-d avatar Mar 09 '20 21:03 s-h-a-r-d

Similar to https://github.com/nextcloud/server/issues/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.

kesselb avatar Mar 09 '20 21:03 kesselb

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?

cvandesande avatar Mar 28 '20 13:03 cvandesande

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.

lukasjuhrich avatar Apr 10 '20 10:04 lukasjuhrich

@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 avatar Apr 10 '20 12:04 kesselb

See also #13721

J0WI avatar Nov 13 '20 21:11 J0WI

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)

khumarahn avatar Nov 19 '21 22:11 khumarahn 

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 avatar Dec 08 '21 14:12 cartman29

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.

enoch85 avatar Jan 05 '22 12:01 enoch85

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

tennox avatar Feb 04 '22 02:02 tennox

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)

hyperlink-skylight avatar Feb 23 '22 09:02 hyperlink-skylight

All versions are affected, but <22 is eol.

J0WI avatar Mar 02 '22 09:03 J0WI

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.

aknrdureegaesr avatar May 04 '23 14:05 aknrdureegaesr

Hi, please update to 25.0.7 or better 26.0.2 and report back if it fixes the issue. Thank you!

My goal is to add a label like e.g. 26-feedback to this ticket of an up-to-date major Nextcloud version where the bug could be reproduced. However this is not going to work without your help. So thanks for all your effort!

If you don't manage to reproduce the issue in time and the issue gets closed but you can reproduce the issue afterwards, feel free to create a new bug report with up-to-date information by following this link: https://github.com/nextcloud/server/issues/new?assignees=&labels=bug%2C0.+Needs+triage&template=BUG_REPORT.yml&title=%5BBug%5D%3A+

szaimen avatar May 22 '23 09:05 szaimen

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)

KopfKrieg avatar May 22 '23 21:05 KopfKrieg

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 avatar Jul 07 '23 02:07 julio-horna

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

julio-horna avatar Jul 15 '23 06:07 julio-horna

Just encountered the same problem on 27.0.2.

Irdiism avatar Oct 17 '23 18:10 Irdiism

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)

nodiscc avatar Dec 06 '23 15:12 nodiscc

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

Railsimulatornet avatar Feb 12 '24 13:02 Railsimulatornet

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)

ca5ua1 avatar Feb 22 '24 10:02 ca5ua1

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

J0WI avatar Feb 24 '24 00:02 J0WI

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"

maximelehericy avatar Mar 20 '24 16:03 maximelehericy