Thread: 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