Hi hackers, A colleague of mine, Ante Krešić, got puzzled by the following behavior:
Setup: postgres=# create table inh_test (id serial, value float); CREATE TABLE postgres=# create table inh_child_1 () INHERITS ( inh_test); CREATE TABLE postgres=# create table inh_child_2 () INHERITS ( inh_test); CREATE TABLE postgres=# insert into inh_child_1 values (1,1); INSERT 0 1 postgres=# insert into inh_child_2 values (1,1); INSERT 0 1 Update tuples in first transaction: postgres=# begin; BEGIN postgres=*# update inh_test set value = 2 where value = 1; UPDATE 2 Delete in second transaction while the first is still active: postgres=# delete from inh_test where value = 1; Commit in the first transaction and we get a delete in the second one even though committed values do not qualify after update. postgres=# COMMIT; postgres=# delete from inh_test where value = 1; DELETE 1 The same happens for declarative partitioned tables as well. When working on a table without inheritance / partitioning the result is different, DELETE 0. So what's the problem? According to the documentation [1]: """ UPDATE, DELETE [..] commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. """ It looks like the observed behaviour contradicts the documentation. If we read it literally the second transaction should delete 0 rows, as it does for non-partitioned and non-inherited tables. From what I can tell the observed behavior doesn't contradict the general guarantees promised by READ COMMITTED. Perhaps we should update the documentation for this case, or maybe remove the quoted part of it. Thoughts? [1]: https://www.postgresql.org/docs/current/transaction-iso.html -- Best regards, Aleksander Alekseev
