Re: 5 new entries for FAQ - Mailing list pgsql-general

pgman wrote:
> Dan Armbrust wrote:
> > I liked the FAQ entry written up by the original submitter much better,
> > WRT to the long index issue.  This write up does not contain the actual
> > error message encountered, so its not going to be found by someone
> > looking for a solution to the problem.  It doesn't contain the optional
> > workaround, and it doesn't have a link into the full text searching
> > features as the recommended fix.  I was one of the more recent people
> > to encounter this problem, and I don't think that this FAQ would have
> > helped me (if I even saw it, because google certainly won't pick it up
> > in a way that most people will search when the encounter the issue -
> > they are going to put in the error message)
> > Dan
>
> True, but we don't have an FAQ that hits every single problem users will
> encounter.  We try to make the FAQ concise so most people will read all
> the items.  If the item come up more often, we will consider making it
> more prominent.

I thought again about your issue and I now remember the proper way to
help users in this case is to improve the error message.  I have added a
"hint" message for the case of indexing large values, patch attached.

[ Sorry about a patch to general but it seems necessary in this case.]

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/access/hash/hashinsert.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/hash/hashinsert.c,v
retrieving revision 1.36
diff -c -c -r1.36 hashinsert.c
*** src/backend/access/hash/hashinsert.c    21 Mar 2005 01:23:57 -0000    1.36
--- src/backend/access/hash/hashinsert.c    10 Aug 2005 21:27:50 -0000
***************
*** 88,94 ****
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds hash maximum %lu",
                          (unsigned long) itemsz,
!                         (unsigned long) HashMaxItemSize((Page) metap))));

      /*
       * Compute the target bucket number, and convert to block number.
--- 88,95 ----
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds hash maximum %lu",
                          (unsigned long) itemsz,
!                         (unsigned long) HashMaxItemSize((Page) metap)),
!                  errhint("Values larger than a buffer page cannot be indexed.")));

      /*
       * Compute the target bucket number, and convert to block number.
Index: src/backend/access/nbtree/nbtinsert.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/nbtree/nbtinsert.c,v
retrieving revision 1.121
diff -c -c -r1.121 nbtinsert.c
*** src/backend/access/nbtree/nbtinsert.c    6 Jun 2005 20:22:57 -0000    1.121
--- src/backend/access/nbtree/nbtinsert.c    10 Aug 2005 21:27:51 -0000
***************
*** 392,398 ****
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds btree maximum, %lu",
                          (unsigned long) itemsz,
!                         (unsigned long) BTMaxItemSize(page))));

      /*
       * Determine exactly where new item will go.
--- 392,401 ----
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds btree maximum, %lu",
                          (unsigned long) itemsz,
!                         (unsigned long) BTMaxItemSize(page)),
!                  errhint("Values larger than 1/3 of a buffer page cannot be indexed\n"
!                          "Consider a separate column containing an MD5 hash of the value\n"
!                          "or use full text indexing.")));

      /*
       * Determine exactly where new item will go.
Index: src/backend/access/nbtree/nbtsort.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/nbtree/nbtsort.c,v
retrieving revision 1.91
diff -c -c -r1.91 nbtsort.c
*** src/backend/access/nbtree/nbtsort.c    6 Jun 2005 20:22:57 -0000    1.91
--- src/backend/access/nbtree/nbtsort.c    10 Aug 2005 21:27:52 -0000
***************
*** 498,504 ****
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds btree maximum, %lu",
                          (unsigned long) btisz,
!                         (unsigned long) BTMaxItemSize(npage))));

      if (pgspc < btisz || pgspc < state->btps_full)
      {
--- 498,507 ----
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds btree maximum, %lu",
                          (unsigned long) btisz,
!                         (unsigned long) BTMaxItemSize(npage)),
!                  errhint("Values larger than 1/3 of a buffer page cannot be indexed\n"
!                          "Consider a separate column containing an MD5 hash of the value\n"
!                          "or use full text indexing.")));

      if (pgspc < btisz || pgspc < state->btps_full)
      {
Index: src/backend/access/rtree/rtree.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/rtree/rtree.c,v
retrieving revision 1.90
diff -c -c -r1.90 rtree.c
*** src/backend/access/rtree/rtree.c    6 Jun 2005 17:01:22 -0000    1.90
--- src/backend/access/rtree/rtree.c    10 Aug 2005 21:27:52 -0000
***************
*** 733,739 ****
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds rtree maximum, %lu",
                          (unsigned long) newitemsz,
!                         (unsigned long) RTPageAvailSpace)));

      maxoff = PageGetMaxOffsetNumber(page);
      newitemoff = OffsetNumberNext(maxoff);        /* phony index for new
--- 733,740 ----
                  (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
                   errmsg("index row size %lu exceeds rtree maximum, %lu",
                          (unsigned long) newitemsz,
!                         (unsigned long) RTPageAvailSpace),
!                  errhint("Values larger than a buffer page cannot be indexed.")));

      maxoff = PageGetMaxOffsetNumber(page);
      newitemoff = OffsetNumberNext(maxoff);        /* phony index for new