External Storage - Automated tagging breaks occ:scan - PostreSQL

This happens only in combination with external storage.

I have two external storage, one a SMB Share and one local external storage (onedrive folder 500GB)

same issue like https://github.com/nextcloud/files_automatedtagging/issues/75#issue-444872480
but only in combination with external storage

Steps to reproduce:

• add an external storage (local folder or smb)
• Create a hidden tag, e.g. "Folder documents"
• Assign this tag to a folder within the external storage
• Create a rule: Assign tag "document" when file system tag is "folder documents"
• Now do a occ files:scan --all (or just the affected folder)

When I delete the automated tagging rule, the scan works without problems. Or when i remove the hidden tag from the folder.

Expected behaviour

files:scan should finish without problems.

Actual behaviour

I get an error:

#sudo -u www-data php -f /var/www/nextcloud/occ files:scan --all
Starting scan for user 1 out of 8 (xxxxxxx)
Exception during scan: An exception occurred while executing a query: SQLSTATE[25P02]: In failed sql transaction: 7 FEHLER:  aktuelle Transaktion wurde abgebrochen, Befehle werden bis zum Ende der Transaktion ignoriert
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1780): Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert()
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1719): Doctrine\DBAL\Connection->handleDriverException()
#2 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1067): Doctrine\DBAL\Connection->convertExceptionDuringQuery()
#3 /var/www/nextcloud/lib/private/DB/Connection.php(261): Doctrine\DBAL\Connection->executeQuery()
#4 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(345): OC\DB\Connection->executeQuery()
#5 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(281): Doctrine\DBAL\Query\QueryBuilder->execute()
#6 /var/www/nextcloud/lib/private/Files/Cache/Cache.php(493): OC\DB\QueryBuilder\QueryBuilder->execute()
#7 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(389): OC\Files\Cache\Cache->getId()
#8 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(394): OC\Files\Cache\Scanner->scanChildren()
#9 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(394): OC\Files\Cache\Scanner->scanChildren()
#10 /var/www/nextcloud/lib/private/Files/Cache/Scanner.php(342): OC\Files\Cache\Scanner->scanChildren()
#11 /var/www/nextcloud/lib/private/Files/Utils/Scanner.php(256): OC\Files\Cache\Scanner->scan()
#12 /var/www/nextcloud/apps/files/lib/Command/Scan.php(161): OC\Files\Utils\Scanner->scan()
#13 /var/www/nextcloud/apps/files/lib/Command/Scan.php(217): OCA\Files\Command\Scan->scanFiles()
#14 /var/www/nextcloud/apps/recognize/vendor/symfony/console/Command/Command.php(298): OCA\Files\Command\Scan->execute()
#15 /var/www/nextcloud/core/Command/Base.php(177): Symfony\Component\Console\Command\Command->run()
#16 /var/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(1040): OC\Core\Command\Base->run()
#17 /var/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand()
#18 /var/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()
#19 /var/www/nextcloud/lib/private/Console/Application.php(214): Symfony\Component\Console\Application->run()
#20 /var/www/nextcloud/console.php(100): OC\Console\Application->run()
#21 /var/www/nextcloud/occ(11): require_once('...')
#22 {main}

in the PostgreSQL log i can this:

2023-03-30 14:14:18.615 CEST [1261593] nextclouduser@nextcloud_db FEHLER:  doppelter Schlüsselwert verletzt Unique-Const                                                   raint »oc_systemtag_object_mapping_pkey«
2023-03-30 14:14:18.615 CEST [1261593] nextclouduser@nextcloud_db DETAIL:  Schlüssel »(objecttype, objectid, systemtagid                                                   )=(files, 43734, 9)« existiert bereits.
2023-03-30 14:14:18.615 CEST [1261593] nextclouduser@nextcloud_db ANWEISUNG:  INSERT INTO "oc_systemtag_object_mapping"                                                    ("objectid", "objecttype", "systemtagid") VALUES($1, $2, $3)
2023-03-30 14:14:18.617 CEST [1261593] nextclouduser@nextcloud_db FEHLER:  aktuelle Transaktion wurde abgebrochen, Befeh                                                   le werden bis zum Ende der Transaktion ignoriert
2023-03-30 14:14:18.617 CEST [1261593] nextclouduser@nextcloud_db ANWEISUNG:  SELECT "fileid" FROM "oc_filecache" WHERE                                                    ("storage" = $1) AND ("path_hash" = $2)

Server configuration

Operating system: Ubuntu 22.04.2 LTS (Jammy Jellyfish)

Web server: nginx/1.23.3

Database: postgres (PostgreSQL) 15.2 (Ubuntu 15.2-1.pgdg22.04+1)

PHP version: PHP 8.1.2-1ubuntu2.11

Nextcloud version: Nextcloud 25.0.5 Version

Where did you install Nextcloud from: https://download.nextcloud.com/server/releases/latest.tar.bz2

Signing status:

No errors have been found.

List of activated apps:

Enabled:
  - activity: 2.17.0
  - bookmarks: 12.1.0
  - bruteforcesettings: 2.5.0
  - calendar: 4.3.1
  - cfg_share_links: 3.0.2
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contacts: 5.2.0
  - contactsinteraction: 1.6.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - deck: 1.8.3
  - external: 5.0.2
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_accesscontrol: 1.15.1
  - files_archive: 1.1.2
  - files_automatedtagging: 1.15.3
  - files_downloadactivity: 1.16.0
  - files_external: 1.17.0
  - files_fulltextsearch: 25.0.0
  - files_fulltextsearch_tesseract: 25.0.0
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - forms: 3.2.0
  - fulltextsearch: 25.0.0
  - fulltextsearch_elasticsearch: 25.0.0
  - geoblocker: 0.5.7
  - groupfolders: 13.1.1
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - mail: 2.2.5
  - nextcloud_announcements: 1.14.0
  - notes: 4.7.2
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.1
  - previewgenerator: 5.2.1
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - recognize: 3.7.0
  - recommendations: 1.4.0
  - registration: 2.1.0
  - related_resources: 1.0.4
  - richdocuments: 7.1.2
  - richdocumentscode: 22.5.802
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - sharebymail: 1.15.0
  - spreed: 15.0.5
  - support: 1.8.0
  - survey_client: 1.13.0
  - systemtags: 1.15.0
  - tasks: 0.14.5
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - twofactor_totp: 7.0.0
  - updatenotification: 1.15.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - weather_status: 1.5.0
  - workflow_ocr: 1.25.4
  - workflow_script: 1.10.0
  - workflowengine: 2.7.0
Disabled:
  - admin_audit
  - encryption
  - google_synchronization: 1.0.9
  - integration_google: 1.0.9
  - integration_onedrive: 1.1.4
  - suspicious_login
  - user_ldap

Nextcloud configuration:

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "XXXXXXX"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "25.0.5.1",
        "overwrite.cli.url": "xxxxxxxxx",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "default_phone_region": "DE",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "overwriteprotocol": "https",
        "logtimezone": "Europe\/Berlin",
        "maintenance_window_start": 1,
        "auth.bruteforce.protection.enabled": false,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpsecure": "ssl",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "maintenance": false,
        "theme": "",
        "loglevel": 2,
        "enabledPreviewProviders": [
            "OC\\Preview\\Imaginary",
            "OC\\Preview\\PDF",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MSOffice2003",
            "OC\\Preview\\MSOffice2007",
            "OC\\Preview\\MSOfficeDoc",
            "OC\\Preview\\OpenDocument"
        ],
        "preview_imaginary_url": "http:\/\/127.0.0.1:9000",
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "timeout": 0
        },
        "preview_max_x": "500",
        "preview_max_y": "500",
        "jpeg_quality": "90",
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

Are you using external storage, if yes which one: local storage and smb

**No encryption

**No LDAP/ActiveDirectory/Webdav/...

Logs

Nextcloud log (data/owncloud.log)

due to sensitive data, i can sent it via mail.

Start by reproducing occ files:scan --all with an external local or SMB storage and the automated-tagging rule described in the issue. Read apps/files/lib/Command/Scan.php, lib/private/Files/Cache/Scanner.php, and lib/private/Files/Cache/Cache.php, then trace the duplicate insert into oc_systemtag_object_mapping. Done means the scan completes without the PostgreSQL unique-constraint error while the rule and external storage remain enabled.