Re: planner/optimizer question - Mailing list pgsql-performance

From Jochem van Dieten
Subject Re: planner/optimizer question
Date
Msg-id 1529.130.161.199.221.1083508247.squirrel@secure.oli.tudelft.nl
Whole thread Raw
In response to Re: planner/optimizer question  (Manfred Koizar <mkoi-pg@aon.at>)
List pgsql-performance
Manfred Koizar said:
>
> As Tom has explained in a nearby message his concern is that --
> unlike dead-to-all -- visible-to-all starts as false, is set to true
> at some point in time, and is eventually set to false again.
> Problems arise if one backend wants to set visible-to-all to true
> while at the same time another backend wants to set it to false.

Got it, I misinterpreted his concern as "visible-to-all should not be
set to true when the tuple is inserted".


> This could be curable by using a second bit as a deleted flag (might
> be even the same bit that's now used as dead-to-all, but I'm not
> sure).  An index tuple having both the visible flag (formerly called
> visible-to-all) and the deleted flag set would cause a heap tuple
> access to check visibility.

Or in a more generalized way: with 2 bits written at the same time you
can express 4 states. But only 3 actions need to be signalled:
dead-to-all, visible-to-all and check-heap. So we can have 2 states
that both signal check-heap.

The immediate solution to the race condition Tom presented would be to
have the transaction that invalidates the heap tuple switch the index
tuple from the one check-heap state to the other. The transaction that
wants to update to visible-to-all can now see that the state has
changed (but not the meaning) and aborts its change.


> But that leaves the question of what to
> do after the deleting transaction has rolled back.  I see no clean
> way from the visible-and-deleted state to visible-to-all.

I'm afraid I don't know enough about the inner workings of rollbacks
to determine how the scenario "A determines visible-to-all should be
set, B invalidates tuple, B rolls back, C invalidates tuple, C
commits, A reaquires lock on index" would work out. I guess I have
some more reading to do.

But if you don't roll back too often it wouldn't even be a huge
problem to just leave them in visible-and-deleted state until
eventually they go into the dead-to-all state.

Jochem





pgsql-performance by date:

Previous
From: James Thornton
Date:
Subject: Recommended File System Configuration
Next
From: Robert Creager
Date:
Subject: Re: Wierd context-switching issue on Xeon