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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
