Re: VACUUM FULL changes the order of rows in a table?

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(at)uic(dot)edu>
To: <pgsql-bugs(at)postgresql(dot)org>
Cc: <trainor(at)uic(dot)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