3 ms·
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
by da_chicken 2y 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().
- zokier 2y 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
- BeefySwain 2y agoWhat transaction mode would you need to make this safe? Can you explain further?
- VWWHFSfQ 2y agoI believe PG will abort the transaction on an exception (PK violation) and the subsequent update will not run in the same context that had original violation. So it could result in a data race. I don't know what isolation level would fix that, if any. My understanding is that in general, if you hit an exception in postgres at all then you can't trust the isolation of the current transaction anymore. That's what MERGE and speculative insertion (on conflict do update) addresses.
- zokier 2y agowell, yes, you are right that exception does abort the transaction. but even if you use something like 'on conflict do nothing' to avoid the exception, you still can get problems with concurrent writes (see my sibling comment https://news.ycombinator.com/item?id=41169638 https://news.ycombinator.com/item?id=41169638) REPEATABLE READ or SERIALIZABLE isolation levels would help with that, as the name suggest repeatable read ensures that the read made by inserts constraint check is repeatable in successive select (or update) statements. https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html is pretty comprehensive.
- da_chicken 2y agoYeah this is correct. I didn't realize it. Most RDBMSs don't behave this way. They allow you to correct exceptions that occur for anything less than a deadlock without a total rollback, but not Postgres.
- da_chicken 2y agoIf that's not safe, then Postgres has not implemented transactions correctly. That seems unlikely, so my guess is that what you think I'm suggesting is not what I'm actually suggesting.
- zokier 2y agothere is nothing wrong postgres transactions, they work exactly as intended, but they aren't magic. this is pretty easy to test: Session 1: psql (16.3 (Debian 16.3-1.pgdg120+1)) Type "help" for help. postgres=# CREATE TABLE tags ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(50) NOT NULL ); CREATE TABLE postgres=# ALTER TABLE tags ADD CONSTRAINT tags_name_unique UNIQUE(name); ALTER TABLE postgres=# INSERT INTO tags (name) VALUES ('A'), ('B') RETURNING *; id | name ----+------ 1 | A 2 | B (2 rows) INSERT 0 2 postgres=# BEGIN; BEGIN postgres=*# INSERT INTO tags (name) VALUES ('B') ON CONFLICT DO NOTHING RETURNING *; id | name ----+------ (0 rows) INSERT 0 0 Session 2: psql (16.3 (Debian 16.3-1.pgdg120+1)) Type "help" for help. postgres=# DELETE FROM tags WHERE name = 'B'; DELETE 1 Session 1 continues: postgres=*# SELECT * FROM tags WHERE name = 'B'; id | name ----+------ (0 rows) postgres=*# END; COMMIT (yes, I added `ON CONFLICT DO NOTHING` to avoid aborting the transaction prematurely)
- da_chicken 2y agoI did some more research. Postgres doesn't handle transactions correctly. Once an error on a constraint occurs, subsequent commands are ignored. You can't handle errors in a transaction. Most RDBMSs don't require a rollback in that situation. It's weird.