Re: Alter Table + Default Value + Serializable

=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= <slardiere(at)hi-media(dot)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