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