QueryException (Oracle 1000 expressions limit) thrown on PostgreSQL during background cron file scan

[asus60]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

When running the background file scanner (ScanFiles.php), a QueryException occurs during metadata cleanup (IndexRequestService::dropIndexForFiles).

Even though the actual backend database is PostgreSQL, Nextcloud throws an error stating: "More than 1000 expressions in a list are not allowed on Oracle."

It appears that OC\DB\QueryBuilder\QueryBuilder artificially limits the IN clause to 1,000 expressions and hardcodes this Oracle-specific error message, regardless of the underlying database driver. Since my setup runs on PostgreSQL (which natively supports larger IN clauses), this restriction is causing an unnecessary crash.

Steps to reproduce

1. Setup Nextcloud with a PostgreSQL database backend.
2. Maintain a large directory cache where a massive number of files (e.g., tens of thousands) are handled, modified, or removed simultaneously.
3. Trigger the background cron job (php /var/www/html/cron.php).
4. The job crashes when the file scanner triggers a metadata index drop with over 47,000 entries.

Expected behavior

The background cron job should successfully execute file scanning and drop metadata cache without triggering an artificial 1,000-expression limit, especially since PostgreSQL natively handles large numbers of expressions in an IN clause.

Nextcloud Server version

34

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.3

Web server

Apache (supported)

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Updated from a MINOR version (ex. 32.0.1 to 32.0.2)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "one-click-instance": true,
        "one-click-instance.user-limit": 100,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "check_data_directory_permissions": false,
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "password": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 3,
            "read_timeout": 10
        },
        "overwritehost": "data.tamc.kr",
        "overwriteprotocol": "https",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "data.tamc.kr"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "34.0.1.2",
        "overwrite.cli.url": "https:\/\/data.tamc.kr\/",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "updatechecker": true,
        "loglevel": 2,
        "log_type": "file",
        "logfile": "\/var\/www\/html\/data\/nextcloud.log",
        "log_rotate_size": 10485760,
        "log.condition": {
            "apps": [
                "admin_audit"
            ]
        },
        "preview_max_x": 2048,
        "preview_max_y": 2048,
        "jpeg_quality": 60,
        "enabledPreviewProviders": {
            "1": "OC\\Preview\\Image",
            "2": "OC\\Preview\\MarkDown",
            "3": "OC\\Preview\\MP3",
            "4": "OC\\Preview\\TXT",
            "5": "OC\\Preview\\OpenDocument",
            "6": "OC\\Preview\\Movie",
            "7": "OC\\Preview\\Krita",
            "0": "OC\\Preview\\Imaginary",
            "23": "OC\\Preview\\ImaginaryPDF",
            "24": "OCA\\Eurooffice\\Preview"
        },
        "enable_previews": true,
        "upgrade.disable-web": true,
        "mail_smtpmode": "smtp",
        "trashbin_retention_obligation": "auto, 30",
        "versions_retention_obligation": "auto, 30",
        "activity_expire_days": 30,
        "simpleSignUpLink.shown": false,
        "share_folder": "\/Shared",
        "one-click-instance.link": "https:\/\/nextcloud.com\/all-in-one\/",
        "upgrade.cli-upgrade-link": "https:\/\/github.com\/nextcloud\/all-in-one\/discussions\/2726",
        "updatedirectory": "\/nc-updater",
        "maintenance_window_start": 100,
        "allow_local_remote_servers": true,
        "davstorage.request_timeout": 3600,
        "documentation_url.server_logs": "https:\/\/github.com\/nextcloud\/all-in-one\/discussions\/5425",
        "htaccess.RewriteBase": "\/",
        "dbpersistent": false,
        "auth.bruteforce.protection.enabled": true,
        "ratelimit.protection.enabled": true,
        "files_external_allow_create_new_local": false,
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "preview_imaginary_url": "***REMOVED SENSITIVE VALUE***",
        "preview_imaginary_key": "***REMOVED SENSITIVE VALUE***",
        "telemetry.enabled": false,
        "data-fingerprint": "734a5e90ce8672d78ca1f51066f7285c",
        "app_install_overwrite": [
            "nextcloud-aio",
            "terms_of_service"
        ],
        "updater.release.channel": "stable",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": true,
        "mail_smtpport": "465",
        "mail_sendmailmode": "smtp",
        "mail_smtpstreamoptions": {
            "ssl": {
                "allow_self_signed": false,
                "verify_peer": true,
                "verify_peer_name": true
            }
        },
        "serverid": 445,
        "log_type_audit": "file",
        "logfile_audit": "\/var\/www\/html\/data\/audit.log",
        "update_channel": "stable",
        "eurooffice": {
            "editors_check_interval": 0,
            "jwt_secret": "2abc82be8819b7ef5b82b5695bb42b4c9e07bea96c7998a9",
            "jwt_header": "AuthorizationJwt"
        },
        "DOMAIN": "data.tamc.kr",
        "AIO_VERSION": "v13.3.1"
    }
}

List of activated Apps

Enabled:
  - activity: 7.0.0
  - admin_audit: 1.24.0
  - appstore: 1.0.0
  - bruteforcesettings: 7.0.0
  - calendar: 6.4.2
  - circles: 34.0.0
  - cloud_federation_api: 1.18.0
  - comments: 1.24.0
  - contacts: 8.5.0
  - contactsinteraction: 1.15.0
  - dashboard: 7.14.0
  - dav: 1.39.0
  - federatedfilesharing: 1.24.0
  - files: 2.6.0
  - files_downloadlimit: 5.2.0-dev.0
  - files_external: 1.26.0
  - files_lock: 34.0.0
  - files_pdfviewer: 7.0.0-dev.0
  - files_reminders: 1.7.0
  - files_sharing: 1.26.0
  - files_trashbin: 1.24.0
  - files_versions: 1.27.0
  - firstrunwizard: 7.0.0-dev.0
  - logreader: 7.0.0
  - lookup_server_connector: 1.22.0
  - nextcloud-aio: 0.8.0
  - nextcloud_announcements: 6.0.0
  - notes: 5.0.0
  - notifications: 7.0.0-dev.1
  - notify_push: 1.3.3
  - oauth2: 1.22.0
  - office: 1.0.0
  - password_policy: 6.0.0-dev.0
  - photos: 7.0.0
  - privacy: 6.0.0-dev.1
  - profile: 1.3.0
  - provisioning_api: 1.24.0
  - recommendations: 7.0.0
  - related_resources: 5.0.0-dev.0
  - serverinfo: 6.0.0
  - settings: 1.17.0
  - sharebymail: 1.24.0
  - sharereview: 2.2.0
  - support: 6.0.0
  - survey_client: 6.0.0-dev.0
  - systemtags: 1.24.0
  - text: 8.0.0
  - theming: 2.9.0
  - twofactor_backupcodes: 1.23.0
  - twofactor_totp: 16.0.0
  - updatenotification: 1.24.0
  - user_status: 1.14.0
  - viewer: 7.0.0-dev.0
  - weather_status: 1.14.0
  - webhook_listeners: 1.6.0
  - whiteboard: 1.5.9
  - workflowengine: 2.16.0
Disabled:
  - app_api: 34.0.0 (installed 5.0.2)
  - deck: 1.17.1 (installed 1.17.1)
  - encryption: 2.22.0
  - federation: 1.24.0 (installed 1.22.0)
  - files_downloadactivity: 1.18.1 (installed 1.18.1)
  - mail: 5.8.0 (installed 5.8.0)
  - spreed: 23.0.6 (installed 23.0.6)
  - suspicious_login: 12.0.0-dev.0
  - tasks: 0.17.1 (installed 0.17.1)
  - terms_of_service: 4.6.1 (installed 4.6.1)
  - transfer_quota_monitor: 1.0.8 (installed 1.0.8)
  - twofactor_nextcloud_notification: 8.0.0
  - user_ldap: 1.25.0
  - x2mail: 0.6.3 (installed 0.6.3)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

{"reqId":"tCg6AfFMAl0An8evgTbd","level":3,"time":"2026-07-06T16:19:00+00:00","remoteAddr":"","user":"--","app":"core","method":"","url":"--","scriptName":"/var/www/html/cron.php","message":"More than 1000 expressions in a list are not allowed on Oracle.","userAgent":"--","version":"34.0.1.2","occ_command":["/var/www/html/cron.php"],"exception":{"Exception":"Doctrine\\DBAL\\Query\\QueryException","Message":"More than 1000 expressions in a list are not allowed on Oracle.","Code":0,"Trace":[{"file":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","line":274,"function":"prepareForExecute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/FilesMetadata/Service/IndexRequestService.php","line":201,"function":"executeStatement","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->","args":[]},{"file":"/var/www/html/lib/private/FilesMetadata/FilesMetadataManager.php","line":231,"function":"dropIndexForFiles","class":"OC\\FilesMetadata\\Service\\IndexRequestService","type":"->","args":[[0,1,2,3,4,"And 46995 more entries, set log level to debug to see all entries"]]},{"file":"/var/www/html/lib/private/FilesMetadata/Listener/MetadataDelete.php","line":50,"function":"deleteMetadataForFiles","class":"OC\\FilesMetadata\\FilesMetadataManager","type":"->","args":[13,[0,1,2,3,4,"And 46995 more entries, set log level to debug to see all entries"]]},{"file":"/var/www/html/lib/private/EventDispatcher/ServiceEventListener.php","line":57,"function":"handle","class":"OC\\FilesMetadata\\Listener\\MetadataDelete","type":"->","args":[{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"}]},{"file":"/var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php","line":220,"function":"__invoke","class":"OC\\EventDispatcher\\ServiceEventListener","type":"->","args":[{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"},"OCP\\Files\\Cache\\CacheEntriesRemovedEvent",{"__class__":"Symfony\\Component\\EventDispatcher\\EventDispatcher"}]},{"file":"/var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php","line":56,"function":"callListeners","class":"Symfony\\Component\\EventDispatcher\\EventDispatcher","type":"->","args":[[{"__class__":"Closure"},{"__class__":"Closure"}],"OCP\\Files\\Cache\\CacheEntriesRemovedEvent",{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"}]},{"file":"/var/www/html/lib/private/EventDispatcher/EventDispatcher.php","line":73,"function":"dispatch","class":"Symfony\\Component\\EventDispatcher\\EventDispatcher","type":"->","args":[{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"},"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"]},{"file":"/var/www/html/lib/private/EventDispatcher/EventDispatcher.php","line":86,"function":"dispatch","class":"OC\\EventDispatcher\\EventDispatcher","type":"->","args":["OCP\\Files\\Cache\\CacheEntriesRemovedEvent",{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"}]},{"file":"/var/www/html/lib/private/Files/Cache/Cache.php","line":669,"function":"dispatchTyped","class":"OC\\EventDispatcher\\EventDispatcher","type":"->","args":[{"__class__":"OCP\\Files\\Cache\\CacheEntriesRemovedEvent"}]},{"file":"/var/www/html/lib/private/Files/Cache/Cache.php","line":581,"function":"removeChildren","class":"OC\\Files\\Cache\\Cache","type":"->","args":[{"__class__":"OC\\Files\\Cache\\CacheEntry"}]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":241,"function":"remove","class":"OC\\Files\\Cache\\Cache","type":"->","args":["ky"]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":138,"function":"removeFromCache","class":"OC\\Files\\Cache\\Scanner","type":"->","args":["ky"]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":307,"function":"scanFile","class":"OC\\Files\\Cache\\Scanner","type":"->","args":["ky",3,-1,null,true]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":577,"function":"scan","class":"OC\\Files\\Cache\\Scanner","type":"->","args":["ky",2,3]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":589,"function":"OC\\Files\\Cache\\{closure}","class":"OC\\Files\\Cache\\Scanner","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/html/lib/private/Files/Cache/Scanner.php","line":576,"function":"runBackgroundScanJob","class":"OC\\Files\\Cache\\Scanner","type":"->","args":[{"__class__":"Closure"},"ky"]},{"file":"/var/www/html/lib/private/Files/Utils/Scanner.php","line":136,"function":"backgroundScan","class":"OC\\Files\\Cache\\Scanner","type":"->","args":[]},{"file":"/var/www/html/apps/files/lib/BackgroundJob/ScanFiles.php","line":55,"function":"backgroundScan","class":"OC\\Files\\Utils\\Scanner","type":"->","args":[""]},{"file":"/var/www/html/apps/files/lib/BackgroundJob/ScanFiles.php","line":139,"function":"runScanner","class":"OCA\\Files\\BackgroundJob\\ScanFiles","type":"->","args":["apples"]},{"file":"/var/www/html/lib/public/BackgroundJob/Job.php","line":50,"function":"run","class":"OCA\\Files\\BackgroundJob\\ScanFiles","type":"->","args":[null]},{"file":"/var/www/html/lib/public/BackgroundJob/TimedJob.php","line":86,"function":"start","class":"OCP\\BackgroundJob\\Job","type":"->","args":[{"__class__":"OC\\BackgroundJob\\JobList"}]},{"file":"/var/www/html/core/Service/CronService.php","line":204,"function":"start","class":"OCP\\BackgroundJob\\TimedJob","type":"->","args":[{"__class__":"OC\\BackgroundJob\\JobList"}]},{"file":"/var/www/html/core/Service/CronService.php","line":107,"function":"runCli","class":"OC\\Core\\Service\\CronService","type":"->","args":["cron",null]},{"file":"/var/www/html/cron.php","line":52,"function":"run","class":"OC\\Core\\Service\\CronService","type":"->","args":[null]}],"File":"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php","Line":232,"message":"More than 1000 expressions in a list are not allowed on Oracle.","query":"DELETE FROM `*PREFIX*files_metadata_index` WHERE `file_id` IN (:dcValue1)","exception":"{\"class\":\"Doctrine\\DBAL\\Query\\QueryException\",\"message\":\"More than 1000 expressions in a list are not allowed on Oracle.\",\"code\":0,\"file\":\"/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php:232\",\"trace\":\"#0 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(274): OC\\DB\\QueryBuilder\\QueryBuilder->prepareForExecute()\\n#1 /var/www/html/lib/private/FilesMetadata/Service/IndexRequestService.php(201): OC\\DB\\QueryBuilder\\QueryBuilder->executeStatement()\\n#2 /var/www/html/lib/private/FilesMetadata/FilesMetadataManager.php(231): OC\\FilesMetadata\\Service\\IndexRequestService->dropIndexForFiles(Array)\\n#3 /var/www/html/lib/private/FilesMetadata/Listener/MetadataDelete.php(50): OC\\FilesMetadata\\FilesMetadataManager->deleteMetadataForFiles(13, Array)\\n#4 /var/www/html/lib/private/EventDispatcher/ServiceEventListener.php(57): OC\\FilesMetadata\\Listener\\MetadataDelete->handle(Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent))\\n#5 /var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php(220): OC\\EventDispatcher\\ServiceEventListener->__invoke(Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent), 'OCP\\\\Files\\\\Cache...', Object(Symfony\\Component\\EventDispatcher\\EventDispatcher))\\n#6 /var/www/html/3rdparty/symfony/event-dispatcher/EventDispatcher.php(56): Symfony\\Component\\EventDispatcher\\EventDispatcher->callListeners(Array, 'OCP\\\\Files\\\\Cache...', Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent))\\n#7 /var/www/html/lib/private/EventDispatcher/EventDispatcher.php(73): Symfony\\Component\\EventDispatcher\\EventDispatcher->dispatch(Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent), 'OCP\\\\Files\\\\Cache...')\\n#8 /var/www/html/lib/private/EventDispatcher/EventDispatcher.php(86): OC\\EventDispatcher\\EventDispatcher->dispatch('OCP\\\\Files\\\\Cache...', Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent))\\n#9 /var/www/html/lib/private/Files/Cache/Cache.php(669): OC\\EventDispatcher\\EventDispatcher->dispatchTyped(Object(OCP\\Files\\Cache\\CacheEntriesRemovedEvent))\\n#10 /var/www/html/lib/private/Files/Cache/Cache.php(581): OC\\Files\\Cache\\Cache->removeChildren(Object(OC\\Files\\Cache\\CacheEntry))\\n#11 /var/www/html/lib/private/Files/Cache/Scanner.php(241): OC\\Files\\Cache\\Cache->remove('ky')\\n#12 /var/www/html/lib/private/Files/Cache/Scanner.php(138): OC\\Files\\Cache\\Scanner->removeFromCache('ky')\\n#13 /var/www/html/lib/private/Files/Cache/Scanner.php(307): OC\\Files\\Cache\\Scanner->scanFile('ky', 3, -1, NULL, true)\\n#14 /var/www/html/lib/private/Files/Cache/Scanner.php(577): OC\\Files\\Cache\\Scanner->scan('ky', 2, 3)\\n#15 /var/www/html/lib/private/Files/Cache/Scanner.php(589): OC\\Files\\Cache\\Scanner->OC\\Files\\Cache\\{closure}()\\n#16 /var/www/html/lib/private/Files/Cache/Scanner.php(576): OC\\Files\\Cache\\Scanner->runBackgroundScanJob(Object(Closure), 'ky')\\n#17 /var/www/html/lib/private/Files/Utils/Scanner.php(136): OC\\Files\\Cache\\Scanner->backgroundScan()\\n#18 /var/www/html/apps/files/lib/BackgroundJob/ScanFiles.php(55): OC\\Files\\Utils\\Scanner->backgroundScan('')\\n#19 /var/www/html/apps/files/lib/BackgroundJob/ScanFiles.php(139): OCA\\Files\\BackgroundJob\\ScanFiles->runScanner('apples')\\n#20 /var/www/html/lib/public/BackgroundJob/Job.php(50): OCA\\Files\\BackgroundJob\\ScanFiles->run(NULL)\\n#21 /var/www/html/lib/public/BackgroundJob/TimedJob.php(86): OCP\\BackgroundJob\\Job->start(Object(OC\\BackgroundJob\\JobList))\\n#22 /var/www/html/core/Service/CronService.php(204): OCP\\BackgroundJob\\TimedJob->start(Object(OC\\BackgroundJob\\JobList))\\n#23 /var/www/html/core/Service/CronService.php(107): OC\\Core\\Service\\CronService->runCli('cron', NULL)\\n#24 /var/www/html/cron.php(52): OC\\Core\\Service\\CronService->run(NULL)\\n#25 {main}\"}","CustomMessage":"More than 1000 expressions in a list are not allowed on Oracle."}}

Additional info

• Deployment: Nextcloud AIO
• Database: PostgreSQL 18.4 on x86_64-pc-linux-musl
• Database Size: ~7061 MB
• Root Cause Location: lib/private/FilesMetadata/Service/IndexRequestService.php line 201 calls executeStatement() with over 47,000 file IDs inside an IN clause array, hitting Nextcloud's built-in 1,000 expression limit safety check.

[susnux]

Thank you for the report!
This is caused because we expect every method call to respect the limits of the DBs we support.
So in this case a chunking is missing.