1. 23
  1.  

  2. 1

    One way to sidestep the counter problems is to insert rows into a shorturl_hits table for every click and dynamically compute the hits based on that.

    1. 1

      Yeah, that’s a different design that avoides the problem all together. It can also provide more information, like when the click was made.

    2. 1

      the idiomatic way to do this is to use postgresql’s INSERT ON CONFLICT DO UPDATE which solves most (all?) problems outlined in the article.

      https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

      1. 2

        ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.

        If I understand the documentation correctly, your suggestion would not try to generate a new key for target_url, as the author expects, but rather update the URL with the new value, which would result in a bug. Is that correct?

        1. 1

          sorry, i skimmed too quickly 🙃 thought it was about unique urls but it’s about clashing unique random identifiers.

          anyway, various behaviours can be implemented with postgresql. there’s also ON CONFLICT DO NOTHING. and there’s RETURNING to retrieve inserted data, so the app can use this to detect a clash and try again.

          and this will all be in a transaction and hence atomic. so even if a concurrent conflicting earlier transaction would try to claim that identifier but then ROLLBACK, for example, the other one would wait for it, and successfully claim that identifier after all.