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

Re: After updating dataset the record goes to the end of the dataset

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Nacef LABIDI <nacef(dot)l(at)gmail(dot)com>
Cc: Roland Voegtli <voegtli(at)scnat(dot)ch>, pgsql-sql(at)postgresql(dot)org
Subject: Re: After updating dataset the record goes to the end of the dataset
Date: 2008-04-22 12:46:30
Message-ID: 480DDE26.50405@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Bart DegryseDate: 2008-04-22 12:56:00
Subject: Re: After updating dataset the record goes to the end of thedataset
Previous:From: Craig RingerDate: 2008-04-22 12:30:28
Subject: Re: Problems with function while migrating from Windows to Linux

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