WARNING: Not sure if what I am about to describe is a bug.
The PostgreSQL 7.2.1 Documentation for VACUUM says:
"VACUUM FULL does more extensive processing, including moving of tuples
across blocks to try to compact the table to the minimum number of disk blocks."
Does above quote explain why the order of rows in an unindexed table would
change after doing a VACUUM FULL on that table with PostgreSQL version 7.1.3?
Would this be considered bug or am I just misunderstanding something?
I am trying to simplify my code to get a tiny working example, but before I spend
a lot of time on that, is there any reason the row order would change?
Scenario:
(1) i have a table with two dozen or so VARCHAR fields.
(2) i populate the table with data in a certain order.
(3) i ALTER TABLE and ADD a TEXT field.
(4) i SET the new text field to a constant, say 'foo'.
(5) i VACUUM FULL the table.
It doesn't matter if I index the table and then cluster it on that index.
Step (4) and the VACUUM FULL in (5) is necessary for the row order to change.
Everything is fine if I do (1)(2)(3)(5).
Everything is fine if I do (1)(2)(3)(4) and tweak (5) so as to just VACUUM (no FULL).
It's like the table was re-ordered in DESCENDING order...
I peeked at vacuum.c, but I think I should go back to simplifying the
example... :-)
douglas
