From: | Zhenghua Lyu <zlv(at)pivotal(dot)io> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Questions of 'for update' |
Date: | 2019-06-10 06:00:57 |
Message-ID: | CAO0i4_QCf8LUCO9xDgDpJ0zdsyM7q83APtqHamdsswQ6NVT3ZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I am reading the code that generating plan for `rowmarks` of Postgres
9.4 (
https://github.com/postgres/postgres/blob/REL9_4_STABLE/src/backend/optimizer/plan/planner.c#L2070
)
After emitting the `LockRows` plannode, the results cannot be considered
in order, and there are comments there:
/*
* The result can no longer be assumed sorted, since locking might
* cause the sort key columns to be replaced with new values.
*/
I do not understand the reason and after some guess, I come up with a case:
```
create table t(c int);
insert into t values (1), (2), (3), (4);
-- Transaction 1
begin;
update t set c = 999 where c = 1; -- change the smallest value to a very
big one
-- transaction 1 not commit yet
-- Transaction 2, another session
begin;
select * from t order by c limit 1 for update; -- Want to find the smallest
value, and then update it
-- this transaction will be blocked by transaction 1
-- then, transaction 1 commit and transaction 2 will return the tuple with
value 999
```
I think the reason is that EvalPlanQual does not check the order.
I try this case under mysql, it will output 2 (which is the correct value
for the meaning of smallest).
So, in summary, my questions are:
1. why after emitting `lockrows` plannode, the result can no longer be
assumed sorted?
2. Is the case above a bug or a feature?
Thanks!
Best Regards,
Zhenghua Lyu
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2019-06-10 06:24:28 | Re: Bloom Indexes - bit array length and the total number of bits (or hash functions ?? ) ! |
Previous Message | Michael Paquier | 2019-06-10 05:35:58 | Re: doc: clarify "pg_signal_backend" default role |