4 ms·

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 go

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 …”.

paulryanrogers 3y ago
And MERGE
mattashii 3y ago
Yes, that's been a thing since 9.5, which was released in early 2016.
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.
  - 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?
inferiorhuman 3y ago
Oh I'll add to the pile: the Postgres ON CONFLICT/MERGE stuff doesn't capture the "select or insert if row doesn't exist" behavior some folks might want to use an upsert type thing for. 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.
- paulddraper 3y ago
  It's true. Fortunately the records are locked, so a second query will pick them up. Still sad tho. Prepared statements work fine with CTEs.
  - inferiorhuman 3y ago
    Prepared statements work fine with CTEs. My experience was that this was not the case. Basically the CTE was materialized and the variables were resolved as part of PREPARE, so each invocation got the same values. I couldn't find the blurb I'd read previously but I did a little digging today and it looks like maybe NOT MATERIALIZED would fix the problem?
    - paulddraper 3y ago
      CTEs used to be an optimization fence, with or without prepared statements. PG 14 or something changed that.
- jadbox 3y ago
  > "select or insert if row doesn't exist" behavior 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.

paulryanrogers 3y ago

And MERGE

mattashii 3y ago

Yes, that's been a thing since 9.5, which was released in early 2016.

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.
- 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?

inferiorhuman 3y ago

Oh I'll add to the pile: the Postgres ON CONFLICT/MERGE stuff doesn't capture the "select or insert if row doesn't exist" behavior some folks might want to use an upsert type thing for. 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.

paulddraper 3y ago
It's true. Fortunately the records are locked, so a second query will pick them up. Still sad tho. Prepared statements work fine with CTEs.
- inferiorhuman 3y ago
  Prepared statements work fine with CTEs. My experience was that this was not the case. Basically the CTE was materialized and the variables were resolved as part of PREPARE, so each invocation got the same values. I couldn't find the blurb I'd read previously but I did a little digging today and it looks like maybe NOT MATERIALIZED would fix the problem?
  - paulddraper 3y ago
    CTEs used to be an optimization fence, with or without prepared statements. PG 14 or something changed that.
jadbox 3y ago
> "select or insert if row doesn't exist" behavior 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.