On Thu, Aug 04, 2005 at 09:19:44AM -0500, Tony Caduto wrote: > I just noticed this, if I do a update like this: > > update new_requests set name = 'tony' where request_id = 2 > > If I do a select * from new_requests that record I just updated is now > at the bottom , before the update it was at the top? SQL doesn't guarantee any particular row order unless you use ORDER BY. Without ORDER BY, simple queries in PostgreSQL are likely to return rows in an order based on their physical location on disk. You can see this location by looking at a row's ctid field: SELECT ctid, * FROM new_requests; See "System Columns" in the documentation for more information about "hidden" columns like ctid: http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html > Why is Postgresql changing the ordering of the results after a simple > update? > It almost looks like the record is being dropped and then readded to the > end. PostgreSQL uses Multi-Version Concurrency Control (MVCC), which creates a new version of the row. Until you vacuum the table, the old row still exists for the benefit of other transactions that might still have visibility to it. http://www.postgresql.org/docs/8.0/static/mvcc.html http://www.postgresql.org/docs/8.0/static/maintenance.html#VACUUM-FOR-SPACE-RECOVERY New rows are added where PostgreSQL finds room for them, which in your case is apparently at the end of the table. Here's an example: CREATE TABLE foo (id integer, name text); INSERT INTO foo VALUES (1, 'Adam'); INSERT INTO foo VALUES (2, 'Bob'); INSERT INTO foo VALUES (3, 'Charlie'); INSERT INTO foo VALUES (4, 'David'); SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,2) | 2 | Bob (0,3) | 3 | Charlie (0,4) | 4 | David (4 rows) UPDATE foo SET name = 'Billie' WHERE id = 2; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,3) | 3 | Charlie (0,4) | 4 | David (0,5) | 2 | Billie (4 rows) Notice that the new version of the row was added at the end of the table, at ctid (0,5). Now let's vacuum the table, which will free up the old row at (0,2) if no other transactions need it any more. Then we'll update another row and see where it goes: VACUUM foo; UPDATE foo SET name = 'Dwight' WHERE id = 4; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Adam (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,5) | 2 | Billie (4 rows) Notice that the new row with id 4 was added where the old row with id 2 had been, at ctid (0,2). Let's do another update: UPDATE foo SET name = 'Alex' WHERE id = 1; SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,5) | 2 | Billie (0,6) | 1 | Alex (4 rows) The new row was added at the end, because the old version of the row at (0,1) might still be visible to other transactions; likewise for the old version of the row with id 4, which had been at (0,4). Let's do another vacuum and then an update and an insert: VACUUM foo; UPDATE foo SET name = 'Arnold' WHERE id = 1; INSERT INTO foo VALUES (5, 'Ernie'); SELECT ctid, * FROM foo; ctid | id | name -------+----+--------- (0,1) | 1 | Arnold (0,2) | 4 | Dwight (0,3) | 3 | Charlie (0,4) | 5 | Ernie (0,5) | 2 | Billie (5 rows) Notice how new rows and new versions of old rows get put where PostgreSQL finds room for them. This is one of the reasons for doing regular vacuuming: if you're deleting or updating rows from a table, the table will continue to grow unless you free up the old rows (aka "dead tuples") so their space can be reused. Aside from wasting space, dead tuples can slow down queries. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
