On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv@pivotal.io> wrote:
1. why after emitting `lockrows` plannode, the result can no longer be assumed sorted?
The plan corresponding to your select query is as following:
QUERY PLAN ------------------------------- Limit -> LockRows -> Sort Sort Key: c -> Seq Scan on t
In LockRows node, the executer tries to lock each tuple which are provided by the Sort node. In the meantime, it's possible that some transaction updates a tuple (which is to be locked by the current transaction) and gets committed. These changes will be visible to the current transaction if it has a transaction isolation level lesser than REPEATABLE_READ. So, the current transaction needs to check whether the updated tuple still satisfies the qual check (in your query, there is no quals, so it always satisfies). If it satisfies, it returns the updated tuple.
Since, the sort has been performed by an earlier node, the output will no longer be sorted.
2. Is the case above a bug or a feature?
IMHO, it looks like an expected behaviour of a correct transaction management implementation. The argument can be that the snapshot is consistent throughout all the nodes. Whatever tuple you've fetched from the bottom level is locked correctly.