Karsten Hilbert wrote:
On Mon, Oct 08, 2007 at 04:13:45PM -0400, Dave Cramer wrote:
Note: I'm not suggesting that you drop them, I'm just suggesting that
sometimes compromises must be made between the "right" answer and the
practical solution.
Well, we haven't yet encountered a scenario where things
were too slow due to FKs so the decision wasn't necessary
yet.
There's also the first rule of optimization: Don't.
The second: Don't yet.
Yeah, I'd have to agree with that adage. Of course one could argue that
XMIN is a premature optimization.
It is not (intended as) an optimization at all but rather a
mechanism to detect imminent semantic corruption.
I view it as optimization since it avoids a column, and I have no doubt
that it works as advertised.
Tell you what, it has saved our ass on that twice already,
on user machines. An exception was thrown rather than bogus
data been written to the database. In one case I was on-site
to observe it in the wild. The other case was provable by
the log file.
as far as postgresql goes the new HOT patch comes dangerously close to
changing the semantics of XMIN.
I said dangerously close. It doesn't change the semantics but
postgresql is developing faster than
it has in the past due to more companies, and more exposure.
I feared as much. I'll have to read up on HOT again. From
the 8.3 bete release notes:
Heap-Only Tuples (Pavan Deolasee, with ideas from many others)
When we update a tuple without changing any of its
indexed columns, and the new version can be stored on
the same heap page, we no longer generate extra index
entries for the new version. Instead, index searches
follow the HOT-chain links to ensure they find the
correct tuple version. In addition, this patch
introduces the ability to "prune" dead tuples on a
per-page basis, without having to do a complete VACUUM
pass to recover space. VACUUM is still needed to clean
up dead index entries, however.
Nothing in here makes me worry XMIN as an indicator of the
last committed transaction might go away. In fact, reading
http://www.mail-archive.com/address@hidden/msg88533.html
shows that xmin-change-at-transaction-commit is never put
to the question (lest I miss something).
Again, the good thing is that all we depend upon is the
broad assumption that XMIN *changes* during TX commit, not
that it changes *in a particular way*. IOW, even if XMIN as
such goes away *something* will have to change, still,
during TX commit. I am so sure about this because that's
part of the very root of MVCC tuple visibility rules. The
only real danger would be if *user* visibiity of that change
indicator goes away so we cannot use it anymore. This is
highly unlikely given PGs track record of exposing more
rather than less state information over the years.
oid's are now gone by default in user tables ....
HOWEVER: This *is* a debatable design decision even if the
actual danger is marginal. Again, I am open to
proposals+patches. (I do know how to solve it technically.)
Use either a version row or a timestamp row and implement optimistic
locking
http://www.agiledata.org/essays/concurrencyControl.html
Dave
|