Alter Table + Default Value + Serializable

Поиск
Список
Период
Сортировка
От Sébastien Lardière
Тема Alter Table + Default Value + Serializable
Дата
Msg-id 4CD42286.10900@hi-media.com
обсуждение исходный текст
Ответы Re: Alter Table + Default Value + Serializable  (Sébastien Lardière <slardiere@hi-media.com>)
Re: Alter Table + Default Value + Serializable  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hi,

I've got a problem with a query run on production system. We've got some
data export in a serializable transaction, and, 2 days ago, someone ran
a DDL ( alter table foo add column ba test default 'blabla'), and then,
the data export is empty. I try to reproduce the scenario below :

begin ;
drop table if exists test ;
create table test ( id serial primary key, t text ) ;
insert into test ( t ) values ( 'test1') ;
insert into test ( t ) values ( 'test2') ;
insert into test ( t ) values ( 'test3') ;
commit ;

-- session 1            |-- session 2
begin ;                 |
alter table test        |
  add column toto int   |
      default 1 ;       |
                        |begin ;
                        |set transaction isolation level serializable ;
                        |select * from test ;
                        |
                        |
commit ;                |
                        | id | t | toto
                        |----+---+------
                        |(0 rows)
                        |
                        |commit ;
                        |
                        |select * from test ;
                        | id |   t   | toto
                        |----+-------+------
                        |  1 | test1 |    1
                        |  2 | test2 |    1
                        |  3 | test3 |    1
                        |(3 rows)


I can't understand why, in the 2nd session, my serialisable transaction
see 0 rows ? It's not true, there is rows.

If the DDL in the first transaction doesn't have 'default 1', the
transaction see the 3 rows. If my transaction in the 2nd session is
'read committed', the same.

What's happen with the the serializable transaction and the default ?

Cheers,

--
Sébastien


В списке pgsql-general по дате отправления:

Предыдущее
От: rmd22
Дата:
Сообщение: Modfying source code to read tuples before and after UPDATE...how to?
Следующее
От: Sébastien Lardière
Дата:
Сообщение: Re: Alter Table + Default Value + Serializable