Re: VACUUM FULL changes the order of rows in a table? - Mailing list pgsql-bugs
From | Scott Shattuck |
---|---|
Subject | Re: VACUUM FULL changes the order of rows in a table? |
Date | |
Msg-id | 023f01c23259$a8cdc150$80c310ac@idearatxp Whole thread Raw |
In response to | VACUUM FULL changes the order of rows in a table? (Douglas Trainor <trainor@uic.edu>) |
List | pgsql-bugs |
Pg 104 of "A Guide to THE SQL STANDARD" Fourth Edition by Date/Darwen says: ... 2. Observe that the definition makes no mention of row ordering. As explained in Chapter 2, the rows of an SQL table have no ordering (and the same is true of true relational tables). It is possible, as we will see in Chapter 10, to impose an ordering on the rows... ; however imposing such an order should not be thought of as "ordering the table," but rather as converting the table into something that is not a table, but instead a sequence or odered list of rows. ... Even the column ordering isn't required to remain consistent although most implementations allow the programmer to get lazy and rely on "select *..." to behave consistently in the absense of an intervening ALTER TABLE or other schema altering event. A pure relational implementation would likely require that you specify both row and column ordering since the underlying data storage is free to be optimized or altered by the implementation for performance reasons. Good programming practices would require that in any case to ensure you were insulated from changes the DBA might choose to make to optimize the schema or allow it to serve multiple applications more efficiently. ss ----- Original Message ----- From: "Douglas Trainor" <trainor@uic.edu> To: <pgsql-bugs@postgresql.org> Cc: <trainor@uic.edu> Sent: Tuesday, July 23, 2002 1:01 AM Subject: [BUGS] VACUUM FULL changes the order of rows in a table? > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
pgsql-bugs by date: