Postgresql Storage Backend Integrity Error

[Natim]

(psycopg2.IntegrityError) duplicate key value violates unique constraint \"idx_records_parent_id_collection_id_last_modified

[Natim]

Refs https://sentry.prod.mozaws.net/operations/kinto-webextensions-prod/issues/395349/

[leplatrem]

This occurred on a request on the records of the crypto collection (/v1/buckets/default/collections/storage-sync-crypto/records) while creating the underlying bucket:

WITH delete_potential_tombstone AS (
            DELETE FROM deleted
             WHERE id = '448cfa42-e013-a3f2-657b-XXXXXXXXXXX'
               AND parent_id = ''
               AND collection_id = 'bucket'
        )
        INSERT INTO records (id, parent_id, collection_id, data, last_modified)
        VALUES ('448cfa42-e013-a3f2-657b-XXXXXXXXXXX', '',
                'bucket', ('{}')::JSONB,
                from_epoch(NULL))

            ON CONFLICT (id, parent_id, collection_id) DO UPDATE
            SET last_modified = EXCLUDED.last_modified

        RETURNING id, as_epoch(last_modified) AS last_modified;

• Isn't it supposed to be the goal of the ON CONFLICT close to handle duplicate key value violates unique constraint errors ?
• Are there concurrent requests on the client that may cause situations like this where two requests on non-existing bucket would overlap? /cc @glasserc

[leplatrem]

Related #602

[glasserc]

The behavior of the client is just to do a sync() on this collection, which only has this one record. This happens before any other access of the default bucket. After the keyring is synced, every extension's collection is synced (in parallel).

[leplatrem]

After the keyring is synced, every extension's collection is synced (in parallel).

Strange then that we ended up with overlaping requests. Let's see if it appears again :)