Re: Toast issues with OldestXmin going backwards

>>>>> "Amit" == Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> writes:

>> Your patch would actually be needed if (and only if) autovacuum was
>> changed back to its old behavior of never vacuuming toast tables
>> independently, and if manual VACUUM pg_toast.*; was disabled. But in
>> the presence of either of those two possibilities, it does nothing
>> useful.

Amit> Yeah, right, I have missed the point that they can be vacuumed
Amit> separately, however, I think that decision is somewhat
Amit> questionable.

Some previous discussion links for reference, for the background to the
thread containing the patch:

https://www.postgresql.org/message-id/flat/87y7gpiqx3.fsf%40oxford.xeocode.com
https://www.postgresql.org/message-id/flat/20080608230348.GD11028%40alvh.no-ip.org

Amit> I think it would have been better if along with decoupling of
Amit> vacuum for main heap and toast tables, we would have come up with
Amit> a way to selectively remove the corresponding rows from the main
Amit> heap, say by just vacuuming heap pages/rows which have toast
Amit> pointers but maybe that is not viable or involves much more work
Amit> without equivalent benefit.

It should be fairly obvious why this is unworkable - most toast-using
tables will have toast pointers on every page, but without making a
whole new index of toast pointer OIDs (unacceptable overhead), it would
be impossible to find the toast pointers pointing to a specific item
without searching the whole rel (in which case we might just as well
have vacuumed it).

Amit> Also, we can think along the lines of another idea suggested by
Amit> Andres [2] on the thread mentioned by you.

That one is tricky for various reasons (locking, order of operations in
vacuum_rel, having to mess with the API of vacuum(), etc.)

--
Andrew (irc:RhodiumToad)