- Implementing "get or create" functionality correctly in PostgreSQL can be tricky, with potential issues around race conditions, concurrency, and bloat.
- A simple INSERT statement is not idempotent, as executing it with the same input twice will trigger a unique constraint violation error.
- To provide idempotency, the process needs to handle two situations: 1) if the tag already exists, return the existing tag, and 2) if the tag does not exist, create it and return the new tag.
- Using a unique constraint violation to handle "get or create" can lead to bloat, as new rows are first inserted and then marked as dead if a duplicate is found.
- Checking if a tag exists before inserting it (the "look before you leap" approach) can suffer from time-of-check to time-of-use issues when used concurrently.
- The "ask for forgiveness" approach using INSERT ON CONFLICT DO NOTHING is a better way to handle "get or create" without generating bloat.
- Concatenating results from the target table and a WITH clause using UNION ALL can handle visibility issues with data-modifying statements in the WITH clause.
- Even the "ask for forgiveness" approach can suffer from concurrency issues, as a race condition can still occur between checking if a tag exists and inserting it.
- Using INSERT ON CONFLICT with a DO NOTHING clause is the most robust solution, providing idempotency, concurrency safety, and preventing bloat.
- Starting in PostgreSQL 17, the MERGE statement with RETURNING can provide an alternative approach that doesn't require unique or exclusion constraints on the target table.