Обсуждение: Possible documentation error
http://www.postgresql.org/docs/8.2/interactive/ddl-system-columns.html has the following statement about ctid: "The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows." I have been testing this statement and find that it seems not quite true. Although ctid changes on update, VACUUM FULL does not change it. What it does do is make lower areas available again so an update after a VACUUM FULL can reuse lower numbers rather than higher ones before. Now it certainly seems to me that it should behave as described given the definition of VACUUM FULL so I am a little confused by my tests. My test table only has two entries in it. Is that the issue? In fact, I find the same behaviour if I do a simple VACUUM on the table. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > I have been testing this statement and find that it seems not quite > true. Although ctid changes on update, VACUUM FULL does not change it. > What it does do is make lower areas available again so an update after a > VACUUM FULL can reuse lower numbers rather than higher ones before. A VACUUM FULL will try to compact a table. Thus if there's a lot of free space at the beginning, it will move tuples near the end to the beginning. > Now it certainly seems to me that it should behave as described given > the definition of VACUUM FULL so I am a little confused by my tests. > My test table only has two entries in it. Is that the issue? In fact, > I find the same behaviour if I do a simple VACUUM on the table. On a table with two entries, VACUUM FULL is going to do nothing of interest. Moving tuples within a page is useless, generally. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Tue, Dec 26, 2006 at 06:12:45PM +0100, Martijn van Oosterhout wrote: > On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > > Now it certainly seems to me that it should behave as described given > > the definition of VACUUM FULL so I am a little confused by my tests. > > My test table only has two entries in it. Is that the issue? In fact, > > I find the same behaviour if I do a simple VACUUM on the table. > > On a table with two entries, VACUUM FULL is going to do nothing of > interest. Moving tuples within a page is useless, generally. A test on a larger table shows the behavior: test=> CREATE TABLE foo (x integer); CREATE TABLE test=> INSERT INTO foo SELECT * FROM generate_series(1, 1000); INSERT 0 1000 test=> DELETE FROM foo WHERE x BETWEEN 2 AND 999; DELETE 998 test=> SELECT ctid, x FROM foo; ctid | x --------+------(0,1) | 1(4,92) | 1000 (2 rows) test=> VACUUM foo; VACUUM test=> SELECT ctid, x FROM foo; ctid | x --------+------(0,1) | 1(4,92) | 1000 (2 rows) test=> VACUUM FULL foo; VACUUM test=> SELECT ctid, x FROM foo;ctid | x -------+------(0,1) | 1(0,2) | 1000 (2 rows) -- Michael Fuhr
On Tue, 26 Dec 2006 18:12:45 +0100 Martijn van Oosterhout <kleptog@svana.org> wrote: > On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > > Now it certainly seems to me that it should behave as described given > > the definition of VACUUM FULL so I am a little confused by my tests. > > My test table only has two entries in it. Is that the issue? In fact, > > I find the same behaviour if I do a simple VACUUM on the table. > > On a table with two entries, VACUUM FULL is going to do nothing of > interest. Moving tuples within a page is useless, generally. I thought that that might be the issue. The docs should probably say "can" instead of "will" then. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Tue, Dec 26, 2006 at 12:49:55PM -0500, D'Arcy J.M. Cain wrote: > On Tue, 26 Dec 2006 18:12:45 +0100 > Martijn van Oosterhout <kleptog@svana.org> wrote: > > On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > > > Now it certainly seems to me that it should behave as described given > > > the definition of VACUUM FULL so I am a little confused by my tests. > > > My test table only has two entries in it. Is that the issue? In fact, > > > I find the same behaviour if I do a simple VACUUM on the table. > > > > On a table with two entries, VACUUM FULL is going to do nothing of > > interest. Moving tuples within a page is useless, generally. > > I thought that that might be the issue. The docs should probably say > "can" instead of "will" then. The doumenttion is accurate as is. It says when "moved by VACUUM FULL". In your case they wern't moved. If you change the word "will" to "can", it will be wrong. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Tue, Dec 26, 2006 at 07:22:21PM +0100, Martijn van Oosterhout wrote: > On Tue, Dec 26, 2006 at 12:49:55PM -0500, D'Arcy J.M. Cain wrote: > > On Tue, 26 Dec 2006 18:12:45 +0100 > > Martijn van Oosterhout <kleptog@svana.org> wrote: > > > On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > > > > Now it certainly seems to me that it should behave as described given > > > > the definition of VACUUM FULL so I am a little confused by my tests. > > > > My test table only has two entries in it. Is that the issue? In fact, > > > > I find the same behaviour if I do a simple VACUUM on the table. > > > > > > On a table with two entries, VACUUM FULL is going to do nothing of > > > interest. Moving tuples within a page is useless, generally. > > > > I thought that that might be the issue. The docs should probably say > > "can" instead of "will" then. > > The doumenttion is accurate as is. It says when "moved by VACUUM FULL". > In your case they wern't moved. If you change the word "will" to "can", > it will be wrong. Howso? There's no guarantee (which is what "will" implies) that a ctid will change on VACUUM FULL. In fact, your example demonstrates that; 0,1 stayed put. I'm sorry if it sounds like I'm picking nits, but using CTID to identify rows could provide a noticeable performance gain in some cases. But users can't make use of that if it's not clear exactly when and how CTIDs can change. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
OK, wording updated. Thanks. --------------------------------------------------------------------------- Jim C. Nasby wrote: > On Tue, Dec 26, 2006 at 07:22:21PM +0100, Martijn van Oosterhout wrote: > > On Tue, Dec 26, 2006 at 12:49:55PM -0500, D'Arcy J.M. Cain wrote: > > > On Tue, 26 Dec 2006 18:12:45 +0100 > > > Martijn van Oosterhout <kleptog@svana.org> wrote: > > > > On Tue, Dec 26, 2006 at 12:04:40PM -0500, D'Arcy J.M. Cain wrote: > > > > > Now it certainly seems to me that it should behave as described given > > > > > the definition of VACUUM FULL so I am a little confused by my tests. > > > > > My test table only has two entries in it. Is that the issue? In fact, > > > > > I find the same behaviour if I do a simple VACUUM on the table. > > > > > > > > On a table with two entries, VACUUM FULL is going to do nothing of > > > > interest. Moving tuples within a page is useless, generally. > > > > > > I thought that that might be the issue. The docs should probably say > > > "can" instead of "will" then. > > > > The doumenttion is accurate as is. It says when "moved by VACUUM FULL". > > In your case they wern't moved. If you change the word "will" to "can", > > it will be wrong. > > Howso? There's no guarantee (which is what "will" implies) that a ctid > will change on VACUUM FULL. In fact, your example demonstrates that; 0,1 > stayed put. > > I'm sorry if it sounds like I'm picking nits, but using CTID to > identify rows could provide a noticeable performance gain in some cases. > But users can't make use of that if it's not clear exactly when and how > CTIDs can change. > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ? HISTORY.html Index: ddl.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.69 diff -c -r1.69 ddl.sgml *** ddl.sgml 28 Nov 2006 01:09:01 -0000 1.69 --- ddl.sgml 30 Dec 2006 20:24:19 -0000 *************** *** 974,980 **** The physical location of the row version within its table. Note that although the <structfield>ctid</structfield> can be used to locate the row version very quickly, a row's ! <structfield>ctid</structfield> will change each time it is updated or moved by <command>VACUUM FULL</>. Therefore <structfield>ctid</structfield> is useless as a long-term row identifier. The OID, or even better a user-defined serial --- 974,980 ---- The physical location of the row version within its table. Note that although the <structfield>ctid</structfield> can be used to locate the row version very quickly, a row's ! <structfield>ctid</structfield> will change if it is updated or moved by <command>VACUUM FULL</>. Therefore <structfield>ctid</structfield> is useless as a long-term row identifier. The OID, or even better a user-defined serial