Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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?

    (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...  :-)



pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group