Re: postgre vs MySQL

On Fri, 2008-03-14 at 08:43 -0700, Steve Crawford wrote:
> > Also, it is MVCC-safe only from 8.3 upwards; on older versions
> > it (incorrectly) deletes dead tuples that are still visible to old
> > transactions.
> >
> >
> More interesting. I may have a broken mental-model. I *thought* that
> CLUSTER acquired exclusive locks and that acquisition of the exclusive
> lock would imply that there couldn't be any transactions accessing that
> table. Where is my misunderstanding?

Here's a scenario:

- transaction A starts to read table A;
- transaction B starts, deletes some records from table B, end ends;
- transaction C starts and clusters table B;
- transaction A finished reading table A, and now tries to read the
records just deleted by transaction B;

Question: under MVCC rules should transaction A see the deleted records
or not ?

Unfortunately I don't know for sure the answer, but if it is yes, then
bad luck for transaction A, because cluster just ate them. And the
locking will not help this...

Cheers,
Csaba.