Re: [HACKERS] Possible documentation error

2006-12-30 Thread Jim C. Nasby
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[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [HACKERS] Possible documentation error

2006-12-30 Thread Bruce Momjian

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[EMAIL PROTECTED]
 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   [EMAIL PROTECTED]
  EnterpriseDBhttp://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 -	1.69
--- ddl.sgml	30 Dec 2006 20:24:19 -
***
*** 974,980 
The physical location of the row version within its table.  Note that
although the structfieldctid/structfield can be used to
locate the row version very quickly, a row's
!   structfieldctid/structfield will change each time it is
updated or moved by commandVACUUM FULL/.  Therefore
structfieldctid/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 structfieldctid/structfield can be used to
locate the row version very quickly, a row's
!   structfieldctid/structfield will change if it is
updated or moved by commandVACUUM FULL/.  Therefore
structfieldctid/structfield is useless as a long-term row
identifier.  The OID, or even better a user-defined serial

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


[HACKERS] Possible documentation error

2006-12-26 Thread D'Arcy J.M. Cain
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.

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] Possible documentation error

2006-12-26 Thread Martijn van Oosterhout
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.


signature.asc
Description: Digital signature


Re: [HACKERS] Possible documentation error

2006-12-26 Thread Michael Fuhr
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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] Possible documentation error

2006-12-26 Thread D'Arcy J.M. Cain
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.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] Possible documentation error

2006-12-26 Thread Martijn van Oosterhout
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.


signature.asc
Description: Digital signature