Re: weirdness with the a sql update

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/