From: Craig Ringer <craig@postnewspapers.com.au>
To: Nacef LABIDI <nacef.l@gmail.com>
Cc: Roland Voegtli <voegtli@scnat.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: After updating dataset the record goes to the end of the dataset
Date: Tue, 22 Apr 2008 20:46:30 +0800
Message-ID: <480DDE26.50405@postnewspapers.com.au> (raw)
In-Reply-To: <f16f7ea00804220310m26753072mec98ef0bc6bb2c9e@mail.gmail.com>
References: <f16f7ea00804220254i671e446au7c50dca45dea10d7@mail.gmail.com>
<3B256469-4A00-44C3-8EB8-F49FA9F7259A@scnat.ch>
<f16f7ea00804220310m26753072mec98ef0bc6bb2c9e@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, nacef.l@gmail.com, voegtli@scnat.ch
Subject: Re: After updating dataset the record goes to the end of the dataset
In-Reply-To: <480DDE26.50405@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox