Does Project:M36 support upserts?

[boogerlad]

That is, if an insert fails due to a unique key constraint, can it instead update the tuple?

see https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT and https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql

[agentm]

No, currently Project:M36 does not support this. However, inserting a tuple into a relation which is already present in the tuple is not an error since it's merely a reassertion of the same truth. This behavior is different from SQL.

TutorialD (master/main): x:=relation{tuple{a 0}}
TutorialD (master/main): :showexpr x
┌──────────┐
│a::Integer│
├──────────┤
│0         │
└──────────┘
TutorialD (master/main): insert x (relation{tuple{a 0}})
TutorialD (master/main): :showexpr x
┌──────────┐
│a::Integer│
├──────────┤
│0         │
└──────────┘

[boogerlad]

Thank you. Your example handles the on conflict do nothing clause well. I was thinking of a scenario where you have a web scraper that gets stock levels of products over time. If a product does not exist in the database, persist its name and stock level. If it does exist, just update its stock level.

TutorialD (master/main): x:=relation{tuple{name "wow", stock 15}}
TutorialD (master/main): :showexpr x
┌──────────┬──────────────┐
│name::Text│stock::Integer│
├──────────┼──────────────┤
│"wow"     │15            │
└──────────┴──────────────┘
TutorialD (master/main): insert x (relation{tuple{name "wow", stock 15}})
TutorialD (master/main): :showexpr x
┌──────────┬──────────────┐
│name::Text│stock::Integer│
├──────────┼──────────────┤
│"wow"     │15            │
└──────────┴──────────────┘
TutorialD (master/main): key unique_name_x {name} x
TutorialD (master/main): insert x (relation{tuple{name "wow", stock 15}})
TutorialD (master/main): :showexpr x
┌──────────┬──────────────┐
│name::Text│stock::Integer│
├──────────┼──────────────┤
│"wow"     │15            │
└──────────┴──────────────┘
TutorialD (master/main): insert x (relation{tuple{name "wow", stock 10}})
ERR: InclusionDependencyCheckError "unique_name_x" Nothing
TutorialD (master/main): :showexpr x
┌──────────┬──────────────┐
│name::Text│stock::Integer│
├──────────┼──────────────┤
│"wow"     │15            │
└──────────┴──────────────┘

Since the on conflict do update behavior does not exist yet, should I open an issue?
I suppose I can always just insert, and if I get an error I can update... That's not recommended on SQL systems, but I don't know why. Perhaps it's safe with Project:M36 because it only supports the maximum isolation level?

[agentm]

Indeed, it would be valuable to have some sort of alternative error handling rather than requiring the user to catch errors. An alternative to catching an error would be to query first if the value is present.

[boogerlad]

If you have time, can you take a look at https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ ?
I don't know enough about databases to see if Project:M36 will run into the same issue with insert + update if fail or query first then insert or update with regards to race conditions.

[agentm]

There are no race conditions in this case because, as you guessed, Project:M36 only supports complete isolation from other transactions. So, it is completely safe to query the database to determine if the key is present, then insert it, if not.

[boogerlad]

Thank you! It's super reassuring to hear it from an expert rather than guessing :D