Deleting all shots works, but shows a 504

[jaredhirsch]

STR:

1. create a screenshot in a test account
2. visit screenshots.firefox.com/leave-screenshots
3. click the 'proceed' button

Expected: shots are deleted

Actual: shots are deleted, but the page hangs for 60 seconds, then shows an nginx 504 Gateway Time-out error.

[jaredhirsch]

Note: If you just sit at the page till it times out, the shots should be gone next time you visit https://screenshots.firefox.com/shots

Workaround: deleting individual shots works fine. Users can remove all their shots one by one.

[ianb]

I see this query in Shot.deleteEverythingForDevice that looks like the one that caused a problem on My Shots:

    return db.select(
      `SELECT DISTINCT devices.id
       FROM devices, devices AS devices2
       WHERE devices.id = $1
             OR (devices.accountid = devices2.accountid
                 AND devices2.id = $1)
      `,
      [deviceId]);

[SoftVision-CosminMuntean]

I have managed to reproduce this issue on latest Nightly (58.0a1) build with Screenshots 19.0.0 and also on Firefox 56.0 release with Screenshots 10.12.0 .

But, it seems that the issue is not reproducible using Screenshots (22.0.0) stage/dev versions installed from here. Probably the issue is only reproducible on production server?

[chenba]

A possible reason why this is happening on prod but not reproducible elsewhere is the size of the database. The patch removes three sequential scans and a sort from the SQL queries involved in deleting all shots.