Re: problem with select after updating

"Raul DAVIDOVICH" <R(dot)DAVIDOVICH(at)cvitkovic-ac(dot)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