Обсуждение: Alter Table + Default Value + Serializable

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

On 11/05/2010 04:28 PM, Sébastien Lardière wrote:
> 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 :

I forgot to mention that this scenario works with 8.3 and 9.0.

--
Sébastien

=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= <slardiere@hi-media.com> writes:
> 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 :

[ serializable transaction reading from recently-rewritten table ]

Yeah, that's going to be a problem.  By the time the serializable
transaction gets to read the altered table, it's a new table all of
whose rows were inserted by the ALTERing transaction.  So none of them
are visible to the serializable transaction's snapshot.  I don't think
there's a lot that can be done about that.  There are some people
working on a reimplementation of serializable mode, but I'm not sure
that it addresses this particular issue; and even if it does, the
likely behavior would be that the serializable transaction would fail
outright rather than give you a surprising view of the table.

It's possible to defend against this type of scenario in the
serializable transaction: lock all the tables you want to touch
before starting the first SELECT.  For instance

    begin;
    set transaction isolation level serializable ;
    lock table test in access share mode;
    select * from test;
    ...

This ensures you don't take your snapshot until any concurrent ALTERs
have committed.  This might not be too practical for everyday work,
of course, but if you have to have a fix that's what to do.

            regards, tom lane

On 11/05/2010 05:19 PM, Tom Lane wrote:
> and even if it does, the
> likely behavior would be that the serializable transaction would fail
> outright rather than give you a surprising view of the table.

thanks for your answer,

I have to say that I would prefer an error in the serializable
transaction, instead of the actual behavior

Nevertheless, thank you, we will lock our tables

regards,

--
Sébastien