Обсуждение: BUG #13541: There is a visibility issue when run some DDL and Query. The time window is very shot

The following bug has been logged on the website:

Bug reference:      13541
Logged by:          zhangjinyu
Email address:      beijing_pg@163.com
PostgreSQL version: 9.2.13
Operating system:   suse
Description:

There is a visibility issue when run some DDL and Query. The time window is
very shot. Postgres is to hold relation lock after getsnapshot. Before a
query holds relation lock, if another session run a DDL(alter table alter
column set datatype), Once this DDL committed, select query will return zero
row, because the DDL(alter table alter column set datatype) will rewrite all
tuples with new transaction id.

We can use the following test case to repro the issue.  we add sleep(40)
after GetTransactionSnapshot (in function exec_simple_query) to enlarge the
time window.
Step1: session1: select * from t; (return 3 row)
                 set enable_sleep30_after_getsnapshot=on;
select * from t;
Step2: session2: begin transaction;
alter table t alter c1 set type char(10);
commit;

we can see session1 "select * from t" return 0 row.
======================================
session1:
select * from t;  (it returns 3 rows)
set enable_sleep30_after_getsnapshot=on;
select * from t;  (it returns 0 row)

session2:
start transaction;
alter table t alter c1 set type char(10);
end;

On Fri, Aug 7, 2015 at 10:16 PM,  <beijing_pg@163.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13541
> Logged by:          zhangjinyu
> Email address:      beijing_pg@163.com
> PostgreSQL version: 9.2.13
> Operating system:   suse
> Description:
>
> There is a visibility issue when run some DDL and Query. The time window is
> very shot. Postgres is to hold relation lock after getsnapshot. Before a
> query holds relation lock, if another session run a DDL(alter table alter
> column set datatype), Once this DDL committed, select query will return zero
> row, because the DDL(alter table alter column set datatype) will rewrite all
> tuples with new transaction id.
>
> We can use the following test case to repro the issue.  we add sleep(40)
> after GetTransactionSnapshot (in function exec_simple_query) to enlarge the
> time window.
> Step1: session1: select * from t; (return 3 row)
>                  set enable_sleep30_after_getsnapshot=on;
> select * from t;
> Step2: session2: begin transaction;
> alter table t alter c1 set type char(10);
> commit;
>
> we can see session1 "select * from t" return 0 row.
> ======================================
> session1:
> select * from t;  (it returns 3 rows)
> set enable_sleep30_after_getsnapshot=on;
> select * from t;  (it returns 0 row)
>
> session2:
> start transaction;
> alter table t alter c1 set type char(10);
> end;

I am not able to reproduce this issue.
I tried putting a breakpoint after GetTransactionSnapshot function
in exec_simple_query function and executed the alter table in other session.

Even if the other session data is committed, still the old data is
present in the
database. The old transaction still can see this data. Am I missing something?

Regards,
Hari Babu
Fujitsu Australia

The test cases is not clear in last mail,  the following shows all test
cases.
1. Table precondition
create table t(c1 int);
create table t2(c1 int):
insert into t values(1),(2),(3);
select xmin from t ;   (xmin=10043)
2. Step1:
   session1:  begin transaction isolation level repeatable read;
                 select * from t2;   (query on other table only for get
snapshot)
   Step2:
  session2: alter table t alter c1 set data type char(10);
               select xmin from t;   (new xid)
   Step3:
  session1:  select xmin from t;       (you can see 0 row, because the
snapshot is the same as the first query when transaction isolation level is
repeatable read.)

The DDL query(alter ...set data type ) rewrites all tuples with new
transaction xid,  so if the transaction isolation level is repeatable read,
that will cause wrong result.   Which DDL query rewrite all tuples with new
xid?




--
View this message in context:
http://postgresql.nabble.com/BUG-13541-There-is-a-visibility-issue-when-run-some-DDL-and-Query-The-time-window-is-very-shot-tp5861304p5862147.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

zhangjinyu <beijing_pg@163.com> writes:
> The DDL query(alter ...set data type ) rewrites all tuples with new
> transaction xid,  so if the transaction isolation level is repeatable read,
> that will cause wrong result.

Yes.  This is unlikely to change, but we ought to document it.

I see that we have documentation warning about this sort of effect in the
TRUNCATE reference page, but not in ALTER TABLE's.  Rather than
copy-and-paste that, ISTM it'd be better to move the warning to somewhere
in mvcc.sgml.

            regards, tom lane