Message-ID: <480DDE26.50405@postnewspapers.com.au>
Date: Tue, 22 Apr 2008 20:46:30 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: Nacef LABIDI <nacef.l@gmail.com>
Cc: Roland Voegtli <voegtli@scnat.ch>, pgsql-sql@postgresql.org
Subject: Re: After updating dataset the record goes to the end of the dataset
References: <f16f7ea00804220254i671e446au7c50dca45dea10d7@mail.gmail.com>	
	<3B256469-4A00-44C3-8EB8-F49FA9F7259A@scnat.ch>
	<f16f7ea00804220310m26753072mec98ef0bc6bb2c9e@mail.gmail.com>
In-Reply-To: <f16f7ea00804220310m26753072mec98ef0bc6bb2c9e@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Nacef LABIDI wrote:
> Yes I don't issue any sort statement, and I indeed want the data to be show
> as it is stored in the database.
That's what you're getting. PostgreSQL has an MVCC design. How it works 
in general terms
(simplified, but I lack the expertise to give a complete explanation 
even if one was required) is:
When you update a row the old row is marked as dead and a new row is 
inserted. If there is no
spare space in the table near the old row (assuming there's any reason 
for the DB to even try
to put the new row near the old one) then the new row will be placed 
elsewhere, such as at
the end of the table.

In other words, after an UPDATE the row really is often at the end of 
the table.

In any case as others have explained you should never rely on the 
database ordering
of records; you should always use an ORDER BY if you care about order. 
The database
makes no guarantees about the order of returned rows.

The database may optimise row retrieval in ways you do not expect. For 
example,
in recent versions of PostgreSQL if there is a sequential scan in 
progress on a table
and you start another query that also runs a sequential scan on the 
table, PostgreSQL
may synchronize the two scans. That'll cause your query to start part 
way through the
table. If the table contains alphabetically ordered data you might get 
something like:

J
K
L
M
... etc ...
A
B
C

So ... in any database, always use ORDER BY if you care about order. 
Just because it usually
works in some databases doesn't mean it won't break just rarely enough 
to drive you insane
while debugging...

--
Craig Ringer