3 ms·

If 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

If 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 ago
there 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 ago
  I 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.
  - anarazel 2y ago
    You can, you just need to use savepoints.
    - zokier 2y ago
      Yeah the error handling is a side-note here. The main thing to understand is that postgresql transactions have "read committed" isolation level by default, which means that reads will see the writes from any committed other transactions, which means that there can be arbitrary changes to data between operations, e.g. between insert and select, even if no errors or conflicts are involved. This is pretty much what the sample I posted in parent comment demonstrates.

If 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 ago

there 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 ago
I 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.
- anarazel 2y ago
  You can, you just need to use savepoints.
  - zokier 2y ago
    Yeah the error handling is a side-note here. The main thing to understand is that postgresql transactions have "read committed" isolation level by default, which means that reads will see the writes from any committed other transactions, which means that there can be arbitrary changes to data between operations, e.g. between insert and select, even if no errors or conflicts are involved. This is pretty much what the sample I posted in parent comment demonstrates.