5 ms·

I am a huge Postgres fan, but “ON CONFLICT UPDATE” does not always cut the mustard. You can’t target conflicts against multiple constraints, so if there are mul

by cdogl 3y ago

I am a huge Postgres fan, but “ON CONFLICT UPDATE” does not always cut the mustard. You can’t target conflicts against multiple constraints, so if there are multiple constraints that could cause conflicts you’re stuck either figuring out whether you can drop a constraint or doing something clever in your client. This is often a smell, but it’s a little inflexible.

richbell 3y ago
In addition to this, `ON CONFLICT` is still susceptible to race-conditions if you have a highly concurrent application. This may be obvious to some, however, a lot of people expect upsert to be atomic and get bit by this. 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.)
- jack_squat 3y ago
  From the Postgres docs, 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 https://www.postgresql.org/docs/current/sql-insert.html What are you referring to?
  - neallindsay 3y ago
    It increments the sequence associated with with a "serial" or "bigserial" field—usually used for primary keys. People are often surprised by this because they expect their primary keys to be sequential and sequences are designed to leave gaps in order to avoid a lot of locking.
    - hn_throwaway_99 3y ago
      That just seems like a fundamental misunderstanding of sequences to me. They are guaranteed to be increasing but not necessarily sequential.
      - richbell 3y ago
        I think it's more confusion of how `ON CONFLICT DO NOTHING` works. Incrementing sequences is not "doing nothing", even if it's valid and sensible behaviour, which leads to confusion.
        cdogl 3y ago
        In fairness to Postgres, “DO NOTHING” is accurate within the SQL domain if you squint. It’s reasonable for queries to have side-effects on the underlying implementation as long as the invariants defined by SQL are met. Surprising, yes, but the alternative would be leaking implementation details in the syntax. 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.
  - richbell 3y ago
    If I recall correctly (and it has been a while, so I'm not saying I am), the issue was with concurrent transactions inserting a record into tableA and another into tableB which has a foreign key constraint to tableA. The issue was likely specific to `ON CONFLICT DO NOTHING` and not `ON CONFLICT DO UPDATE`. 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; However, one or both inserts can fail intermittently because the primary key for `package_type` will be auto-incremented and thus the foreign key won't be valid. Also, as mentioned in another comment[0] this won't work if `maven` already exists in the `package_type` table. [0]: https://news.ycombinator.com/item?id=36031790 https://news.ycombinator.com/item?id=36031790
    - hn_throwaway_99 3y ago
      I think that actually more to the root of the problem, as other folks have noted, is that `ON CONFLICT DO NOTHING` means the RETURNING clause returns no rows if there is a conflict, which in my experience is rarely what people want. So instead people do `ON CONFLICT DO UPDATE` with a no-op update which has performance/locking implications, otherwise they need to do a complicated query (search stack overflow). 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.
    - jack_squat 3y ago
      Atomicity doesn't mean "doesn't fail", it means "either fails or succeeds, but does not succeed halfway". 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.
- paulddraper 3y ago
  It's as resilient to race conditions as it could be. Does exactly what you'd expect, no? That is annoying about sequences, I agree.
  - richbell 3y ago
    > It's as resilient to race conditions *as it could be*. Does exactly what you'd expect, no? 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.
    - paulddraper 3y ago
      Could you describe what you mean? What phenomenon? For the default transaction isolation level, or a different one?
    - vore 3y ago
      It is 100% resilient short of your hard drive going through a microwave. It is almost definitely an application logic error if you are observing non-atomicity. What would non-atomicity even look like here? Only some columns being written?
paulddraper 3y ago
> You can’t target conflicts against multiple constraints, 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?
- richbell 3y ago
  In my experience this tends to be an annoyance when you want to upsert against a table that has multiple unique constraints. 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)`.
  - paulddraper 3y ago
    So....what is gh_id and gh_node_id? Like, you have gh_id | gh_node_id ------------------ 1 | 2 2 | 1 And then you want to "upsert" (1, 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.
    - cropcirclbureau 3y ago
      Ah, I'd run into the lack of multi constraint ON CONFLICT in postgres yesterday and was wondering about the omission. I'd not considered this case and GP's code smell comment makes sense now. 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?

by cdogl 3y ago

richbell 3y ago

In addition to this, `ON CONFLICT` is still susceptible to race-conditions if you have a highly concurrent application. This may be obvious to some, however, a lot of people expect upsert to be atomic and get bit by this. 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.)

jack_squat 3y ago
From the Postgres docs, 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 https://www.postgresql.org/docs/current/sql-insert.html What are you referring to?
- neallindsay 3y ago
  It increments the sequence associated with with a "serial" or "bigserial" field—usually used for primary keys. People are often surprised by this because they expect their primary keys to be sequential and sequences are designed to leave gaps in order to avoid a lot of locking.
  - hn_throwaway_99 3y ago
    That just seems like a fundamental misunderstanding of sequences to me. They are guaranteed to be increasing but not necessarily sequential.
    - richbell 3y ago
      I think it's more confusion of how `ON CONFLICT DO NOTHING` works. Incrementing sequences is not "doing nothing", even if it's valid and sensible behaviour, which leads to confusion.
      - cdogl 3y ago
        In fairness to Postgres, “DO NOTHING” is accurate within the SQL domain if you squint. It’s reasonable for queries to have side-effects on the underlying implementation as long as the invariants defined by SQL are met. Surprising, yes, but the alternative would be leaking implementation details in the syntax. 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.
- richbell 3y ago
  If I recall correctly (and it has been a while, so I'm not saying I am), the issue was with concurrent transactions inserting a record into tableA and another into tableB which has a foreign key constraint to tableA. The issue was likely specific to `ON CONFLICT DO NOTHING` and not `ON CONFLICT DO UPDATE`. 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; However, one or both inserts can fail intermittently because the primary key for `package_type` will be auto-incremented and thus the foreign key won't be valid. Also, as mentioned in another comment[0] this won't work if `maven` already exists in the `package_type` table. [0]: https://news.ycombinator.com/item?id=36031790 https://news.ycombinator.com/item?id=36031790
  - hn_throwaway_99 3y ago
    I think that actually more to the root of the problem, as other folks have noted, is that `ON CONFLICT DO NOTHING` means the RETURNING clause returns no rows if there is a conflict, which in my experience is rarely what people want. So instead people do `ON CONFLICT DO UPDATE` with a no-op update which has performance/locking implications, otherwise they need to do a complicated query (search stack overflow). 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.
  - jack_squat 3y ago
    Atomicity doesn't mean "doesn't fail", it means "either fails or succeeds, but does not succeed halfway". 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.
paulddraper 3y ago
It's as resilient to race conditions as it could be. Does exactly what you'd expect, no? That is annoying about sequences, I agree.
- richbell 3y ago
  > It's as resilient to race conditions *as it could be*. Does exactly what you'd expect, no? 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.
  - paulddraper 3y ago
    Could you describe what you mean? What phenomenon? For the default transaction isolation level, or a different one?
  - vore 3y ago
    It is 100% resilient short of your hard drive going through a microwave. It is almost definitely an application logic error if you are observing non-atomicity. What would non-atomicity even look like here? Only some columns being written?

paulddraper 3y ago

> You can’t target conflicts against multiple constraints, 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?

richbell 3y ago
In my experience this tends to be an annoyance when you want to upsert against a table that has multiple unique constraints. 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)`.
- paulddraper 3y ago
  So....what is gh_id and gh_node_id? Like, you have gh_id | gh_node_id ------------------ 1 | 2 2 | 1 And then you want to "upsert" (1, 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.
  - cropcirclbureau 3y ago
    Ah, I'd run into the lack of multi constraint ON CONFLICT in postgres yesterday and was wondering about the omission. I'd not considered this case and GP's code smell comment makes sense now. 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?