Thread: problem with select after updating
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 happens when using both JDBC or psql. Normally, rows shouldn't
change the order after updates. Am I messing it up with the indexes?
thank you very much in advance
---------------------------------------------------
Raul Davidovich
Responsable Informatique
Cvitkovic & Associés Consultants
(33) 1 45 15 40 68
(33) 1 45 15 40 41 Fax
-------------------------------------------------------
http://www.caconcology.com
SQL doesn't specify how updates/inserts etc. will affect ordering of rows in a table. If you need a specific ordering of results, you always must use ORDER BY. There is no guarantee of ordering otherwise. Bob On Monday 10 December 2001 11:07 am, Raul DAVIDOVICH wrote: > 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 happens when using both JDBC or psql. Normally, rows shouldn't > change the order after updates. Am I messing it up with the indexes? > > thank you very much in advance > > --------------------------------------------------- > Raul Davidovich > Responsable Informatique > Cvitkovic & Associés Consultants > > (33) 1 45 15 40 68 > (33) 1 45 15 40 41 Fax > ------------------------------------------------------- > http://www.caconcology.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
"Raul DAVIDOVICH" <R.DAVIDOVICH@cvitkovic-ac.fr> writes:
> 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 happens when using both JDBC or psql. Normally, rows shouldn't
> change the order after updates. Am I messing it up with the indexes?
You are mistaken to assume that the row order is stable. SQL guarantees
*nothing* about row order unless you use ORDER BY to constrain the
result of a SELECT.
regards, tom lane
"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
On Monday 10 December 2001 02:20 pm, Ian Harding wrote: > Also, isn't an update just a delete followed by an insert, resulting in the > 'inserted' row being at the 'end' of the table? I hear talk of 'physical' > ordering of records in a table, but is there really such a thing? What > determines the order in which orders are returned if no index is specified? My understanding is that Postgres has a "non-overwriting" storage manager, so that means that, yes, when you update a record it does mark the current version of the record as expired (or whatever exactly the term is) and a new version of the record with your update is appended to the database file. The expired versions of records left behind from updates and deletes are available to in-process transactions depending on their transaction isolation level when you changed the row. The expired versions of rows remain in the database file until you run the VACUUM command. The VACUUM command and PostgreSQL's non-overwriting storage is considered to be a problem in using the database for high volatility databases in 24/7 systems. VACUUM must be run every so often for expired records to be purged from disk or else the database files become HUGE and access becomes SLOW. Moreover, during this VACUUM, the table is offline, so for large tables it could mean significant downtime. Like you are thinking, you may find that the last row returned is the row that was last updated or inserted. Still, you should use ORDER BY, because if you read the TODO file in the cvs source, fixing the VACUUM problem is a high priority. Fixing the VACUUM command and allowing reuse of expired rows without VACUUM may change the current behavior whereby the last inserted/updated row is the last row in the physical database file. Bob