From: | Sébastien Lardière <slardiere(at)hi-media(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Alter Table + Default Value + Serializable |
Date: | 2010-11-05 15:28:06 |
Message-ID: | 4CD42286.10900@hi-media.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Sébastien Lardière | 2010-11-05 15:37:05 | Re: Alter Table + Default Value + Serializable |
Previous Message | Tom Lane | 2010-11-05 15:02:38 | Re: Problem with frequent crashes related to semctl |