VACUUM FULL changes the order of rows in a table?

From: Douglas Trainor <trainor(at)uic(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Cc: trainor(at)uic(dot)edu
Subject: VACUUM FULL changes the order of rows in a table?
Date: 2002-07-23 07:01:13
Message-ID: 3D3CFF39.1BAEA14C@uic.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message eutm 2002-07-23 08:21:41 Patch for Re: [HACKERS] Bug of PL/pgSQL parser
Previous Message Tom Lane 2002-07-22 20:39:21 Re: Postgres throwing exception for int8 datatype.