On Mon, 2008-05-26 at 16:55 +0200, Peter Eisentraut wrote:
> Am Montag, 26. Mai 2008 schrieb Simon Riggs:
> > At the moment, a long running SQL Statement can prevent xmin moving
> > forward, which can result in VACUUM and HOT not being able to remove row
> > versions effectively. My proposal is that a Read Uncommitted transaction
> > would not prevent row removal, which then offers no guarantee that the
> > "correct" answer would be returned. Which is *exactly* what that
> > transaction isolation level was designed for.
> >
> > In many cases, an application designer may be able to tell that a
> > particular query will always return the correct answer. For example, we
> > may query against data which is known not to change, even though other
> > data in the same database cluster may be subject to frequent change.
> > e.g. queries against large insert-only tables.
>
> If the data in a table never changes, why would VACUUM or HOT need to touch
> it? The use case isn't clear to me.
A long running transaction on an insert-only table will never not see a
row. It *will* prevent row removal by VACUUM on other tables that are
being updated. This proposal would remove the linkage between the two
situations, allowing benefit for general users.
Tom's objection has made me think some more, however. The user wouldn't
be aware that they had used (the proposal) inappropriately, so it's
really too unsafe for general use. That situation could change if we
were able to mark tables as READ ONLY, INSERT ONLY or INSERT UPDATE
DELETE ALLOWED (default). That's something I'd like to do in the future,
for a variety of optimisations but its not on my list yet.
-- Simon Riggs www.2ndQuadrant.comPostgreSQL Training, Services and Support