The author mentions having to get over the lack of upsert when moving from Oracle. But readers might like to know this isn’t a problem anymore since Postgres got “INSERT … ON CONFLICT UPDATE …”.
Why? You cannot access the row id or any values within the row as a return value unless the row changes. The top stackexchange answer goes into some detail about why you really don't want to simply make a dummy update in Postgres.
You can access these old row values in some scope for the no-update case, and this allows you to leverage CTEs instead of dummy updates. However… you can't use CTEs with prepared statements (and it's a non-obvious fail for the uninitiated). So yeah there are still lots of little sharp edges.
Fortunately the records are locked, so a second query will pick them up. Still sad tho.
Prepared statements work fine with CTEs.
Prepared statements work fine with CTEs.
PG 14 or something changed that.
I've hit this many, many times. It's such a common need, and yet I keep having to write it out as multiple queries to "select row" and if no result, do then insert query.
This is often a smell, but it’s a little inflexible.
I also dislike that `ON CONFLICT ... DO NOTHING` increments numerical primary keys. I understand why it happens but it seems counter-intuitive given the name "DO NOTHING". (And, yes, relying on the values of primary keys to never change is an anti-pattern. However, if you have a table of 10 values and the primary keys have massive gaps like 1, 500_211, 2_521_241, 15_631_121, etc., it feels weird nonetheless.)
ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — “UPDATE or INSERT”.
https://www.postgresql.org/docs/current/sql-insert.html
What are you referring to?
For example, let's saying you're building an index of open source packages and have two tables: package_type(id, name) and package(id, type_id, namespace, name).
If you receive two concurrent requests for `maven://log4j:log4j` and `maven://io.quarkus:quarkus`, a naive implementation to insert both "maven" and the packages if they don't exist might look something like this:
WITH type_id AS (
INSERT INTO package_type(name)
VALUES (:type)
RETURNING id
ON CONFLICT DO NOTHING
)
INSERT INTO package (type_id, namespace, name)
SELECT type_id, :namespace, :name
FROM type
ON CONFLICT DO NOTHING;
There is nothing about what you are describing that is different from the behavior you'd get from a regular insert or update. If two transactions conflict, a rollback will occur. That isn't violating atomicity. In fact, it is the way by which atomicity is guaranteed.
The behavior of sequence values getting incremented and not committed, resulting in gaps in the sequence, is a separate matter, not specific to Postgres or to upsert.
You have two options:
(1) ON CONFLICT DO UPDATE, with dummy update:
WITH
type AS (
INSERT INTO package_type (name)
VALUES ($1)
ON CONFLICT (name) DO UPDATE SET name = excluded.name
RETURNING id
)
INSERT INTO package (type_id, namespace, name)
SELECT id, $2, $3
FROM type
ON CONFLICT (type_id, namespace, name) DO UPDATE SET name = excluded.name
RETURNING id;
INSERT INTO package_type (name)
VALUES ($1)
ON CONFLICT DO NOTHING;
INSERT INTO package (type_id, namespace, name)
SELECT type_id, $2, $3
FROM package_type
WHERE name = $1
ON CONFLICT DO NOTHING;
SELECT id
FROM package
WHERE (type_id, namespace, name) = ($1, $2, $3);I wish that postgres would add some sort of backwards compatible option like `ON CONFLICT DO NOOP` or `ON CONFLICT DO RETURN` so that you got the semantics of `DO NOTHING` except that the conflicted rows are returned.
On the other hand, it’s so easy to use and rely on autoincrement IDs and to assume they are monotonic and predictable based on naive testing. If we could do it all again I’d fuzz the IDs even during normal operation (occasionally increment by a random extra amount) so that they don’t become such a foot gun for developers.
That is annoying about sequences, I agree.
While that that may be true, based on the hours I've spent scouring Stack Overflow and GitHub issues it seems that many people don't realize that it's only 99.999% resilient.
I've never needed that, and it's not clear why I would.
Like, my new record is a duplicate of record A according to one constraint and record B according to another constraint, so it updates both A and B?
One "upsert" but two resulting records?
Can someone fill me in on when this would crop up?
For example, let's say you're tracking GitHub repositories and have a table `repository(id, gh_id, gh_node_id, owner, name, description)` where `gh_id` and `gh_node_id` are both unique.
If you want to insert or update a repository you might want to do something like below, however, this is not a valid syntax and as you need to define a separate `DO UPDATE` for `gh_id` and `gh_node_id`:
INSERT INTO repository (gh_id, gh_node_id, owner, name, description)
VALUES (:id, :node_id, :owner, :name, :description)
ON CONFLICT DO UPDATE
SET name = excluded.name,
description = excluded.description;
To my knowledge there's no way to define a single constraint `UNIQUE(gh_id || gh_node_id)` instead of `UNIQUE(gh_id && gh_node_id)`.
Like, you have
gh_id | gh_node_id
------------------
1 | 2
2 | 1
You want both records to be updated?
I'm still struggling to see why you would want to do a upsert relative to multiple unique constraints at once.
In my specific case, business logic dictates uniqueness of both columns is tied together i.e. if an incoming tuple has a value (1, 2) today, all subsequent expected tuples with `gh_id` 1 will also have `gh_node_id` of 2. What's the best practice to model this constraint?
I'm not saying that multiple unique identifiers is a code smell; I am claiming that an upsert is only sensibly done in the context of one constraint.