How to Get or Create in PostgreSQL

https://hakibenita.com/postgresql-get-or-create

by todsacerdoti • 2 years ago

103 24 2 years ago

shiandow• 2 years ago

Interesting 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• 2 years ago

I 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• 2 years ago

Since 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• 2 years ago

I 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• 2 years ago

SQL 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_chicken4• 2 years ago

It'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().

zokier4• 2 years 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

eknkc• 2 years ago

Oh 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• 2 years ago

If 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

arkh• 2 years 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.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• 2 years ago

I 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• 2 years ago

Small 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• 2 years ago

This 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

rst• 2 years ago

Skip 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• 2 years ago

at 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/

How to Get or Create in PostgreSQL

Related Stories