Re: SSI and Hot Standby

On 01/21/2011 02:21 PM, Florian Pflug wrote:
> Still, the would dump reflects a database state that *logically* never existed (i.e. not in any serial schedule). If you dump for disaster recovery, you might not care. If you dump to copy the data onto some reporting server you might.
>
> best regards,
> Florian Pflug

I am beginning to understand the problem. If you don't mind, here is a
complete example if somebody else is having troubles understanding this.

Let's say we have tables D1 and D2. Both contain a single column, id,
and a single row. The data in the beginning is as follows:

D1: id = 1
D2: id = 1

The constrains: D1.id can only be incremented. Whenever D2.id is
updated, it must be updated to D1.id + 1.

The transactions:
T1: begin; update D1 set id = id + 1;
T2: begin; update D2 set id = (select id+1 from D1);
T1: commit;
T3: begin; select id from D1; select id from D2; commit; Data seen: (2,
1) -- this is a possible state
T2: commit;
T4: begin; select id from D1; select id from D2; commit; Data seen: (2, 2)
This is again a possible state. But if we compare this to the state seen
by T3 this is not valid. From state (2, 1) we can not get to state (2,
2) without breaking one of the constraints. Thus, the state of T3 is not
valid in the database.

So, I finally got it! :-) I hope this example will help somebody else
understand the problem. The problem I had understanding this was that
the state in T3 is in fact perfectly valid. I though that there must be
some problem with that state alone. There isn't, unless you compare it
to the state after T2 has committed.

Thanks to all explaining this to me,
- Anssi