4 ms·
How to Get or Create in PostgreSQL
- shiandow 2y agoInteresting and very educational article. I do wonder why in the first example of inserting multiple tags with get_or_create_tag the id of 'C' suddenly changes to 4. Were some queries perhaps not run in the order they appear?
- CRConrad 2y agoI got the same impresssion, that the demo wasn't implemented as a single "clean" run. Another indication of this is that the next passage, about eliminating "bloat" from failed inserts, shows the table containing two live tuples again, not three or four as it should after the preceding example.
- haki 2y agoSince at least two people noticed, I would just say that the insert-select-insert approach was added later, after I took some inspiration from Django's implementation of get_or_create.
- atemerev 2y agoI wonder why this really common use case requires so much esoteric SQL/Postgres knowledge. While everything is well explained, the solution is not simple, by all means. I don’t like complex solutions to common problems.
- robertlagrant 2y agoSQL is partitioned along the major axes of GET, INSERT, UPDATE, DELETE. Combining any of them just isn't so natural for SQL. Upsert is another one.
- da_chicken 2y agoIt's not that difficult to do in the general case. Usually, you're just doing it through the program. - Open connection - Begin transaction - Do INSERT - If you get a PK violation, do an UPDATE instead - Commit The thing is, while it's a common pattern, it also has many equally common similar patterns. Sometimes you want to try the UPDATE first. Sometimes you want the program to throw an exception if the INSERT fails. Sometimes you're doing something with row versioning or pessimistic locking. It's same reason why your file IO library might have a ReadAllText() method, but only after it has Open() and Read() and ReadLine().
- zokier 2y ago- Begin transaction - Do INSERT - If you get a PK violation, do an UPDATE instead - Commit This is not safe in the default transaction mode, other transactions can delete the row between insert and update/select. Also, as explained by the article, this has the table bloating problem
- BeefySwain 2y agoWhat transaction mode would you need to make this safe? Can you explain further?
- VWWHFSfQ 2y agoI believe PG will abort the transaction on an exception (PK violation) and the subsequent update will not run in the same context that had original violation. So it could result in a data race. I don't know what isolation level would fix that, if any. My understanding is that in general, if you hit an exception in postgres at all then you can't trust the isolation of the current transaction anymore. That's what MERGE and speculative insertion (on conflict do update) addresses.
- zokier 2y agowell, yes, you are right that exception does abort the transaction. but even if you use something like 'on conflict do nothing' to avoid the exception, you still can get problems with concurrent writes (see my sibling comment https://news.ycombinator.com/item?id=41169638 https://news.ycombinator.com/item?id=41169638) REPEATABLE READ or SERIALIZABLE isolation levels would help with that, as the name suggest repeatable read ensures that the read made by inserts constraint check is repeatable in successive select (or update) statements. https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html is pretty comprehensive.
- eknkc 2y agoOh I've been doing the `on conflict do update set id = EXCLUDED.id` thing for ages now. Thought the update would be no-op because nothing changed. Interesting.
- teget 2y agoIf you update a column that is not indexed you might get away with it without stressing the cleanup processes too much (vacuum and friends): https://www.postgresql.org/docs/current/storage-hot.html https://www.postgresql.org/docs/current/storage-hot.html
- arkh 2y ago> Thought the update would be no-op because nothing changed. You can use a specific trigger function just to prevent this: suppress_redundant_updates_trigger https://www.postgresql.org/docs/current/functions-trigger.html https://www.postgresql.org/docs/current/functions-trigger.ht... > The suppress_redundant_updates_trigger function, when applied as a row-level BEFORE UPDATE trigger, will prevent any update that does not actually change the data in the row from taking place. This overrides the normal behavior which always performs a physical row update regardless of whether or not the data has changed.
- agwa 2y agoI just tested that and the trigger causes the INSERT to return no rows if the row already exists. It basically turns the ON CONFLICT DO UPDATE into ON CONFLICT DO NOTHING.
- can3p 2y agoSmall nitpick - the author mentions upsert in the beginning of the article only to "forget" about it and use it in the end (insert .. on conflict ignore) and that's the obvious solution there. The final query is very neat though! And special thanks for mentioning "MERGE ... RETURNING" in PG17, that's really cool
- jillesvangurp 2y agoThis is super informative, thank you. I did a small OSS project around postgresql a while ago to implement a simple document store with Kotlin and a low level co-routine friendly postgresql driver called jasync-postgresql that doesn't do blocking IO. I ended up using a few of the other solutions mentinoed in the article. But based on this, I might want to upgrade it to use MERGE. My intention with this project is to use it for storage and transactions. Most of our query heavy lifting is done via external search engines like Elasticsearch. And my attitude with databases is that if you are not querying on it, it doesn't need separate tables, columns, and indices. The point with a document store is that you only use limited querying strategies. I've used documents stores for various projects for over ten years and that style of persistence just fits a lot of things I do. I'm currently planning to roll this out for one of our products. So, this is not just a hobby project. If somebody has time; I wouldn't mind some eyeballs on this project: https://github.com/formation-res/pg-docstore https://github.com/formation-res/pg-docstore
- deleted 2y ago[deleted]
- rst 2y agoSkip down to the bottom for a simple approach that just works, in sufficiently recent PG releases (MERGE). Back up just a bit for something that's nearly as good, and works with everything since Postgresql 10 (INSERT ... ON CONFLICT). Most of the post describes more complicated approaches that have subtle race conditions, and are worth looking at mainly to understand what the problems are.
- geekodour 2y agoat one point i was using upserts for everything but then had a case where it was impossible to get it working with generated id. wrote about it very roughly here https://blog.geekodour.org/posts/upserts/ https://blog.geekodour.org/posts/upserts/
- deleted 2y ago[deleted]