Re: problem with select after updating

"Raul DAVIDOVICH" <R.DAVIDOVICH@cvitkovic-ac.fr> writes:

> Hello,
>
>      I'm having a problem when updating a row.. after comiting the change,
> the row appears at the end of the list, and from there on, it stays there
> until the next update.

This is normal, see below.

>      This happens when using both JDBC or psql. Normally, rows shouldn't
> change the order after updates. Am I messing it up with the indexes?

There is no defined order for storing rows in PostgreSQL.  If you want
query results in a specific order use an ORDER BY clause.

What's happening is that PostgreSQL never overwrites a row when it's
updated (since other transactions may still need to see the old row),
so it creates a new one and marks the old as invalid.  The new row
will usually come at the end of a set of query results.

ORDER BY is your friend.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863