Re: [HACKERS] functional indexes

>
> Added to open 6.4 bugs list.

    Can be removed from the list.

>
>
> > With the current snapshot, there seems to be problem with functional indexes.
> > Example:
> >
> > CREATE TABLE timelog (loginname text, site varchar(16), start_time datetime,
> > elapsed timespan);
> > CREATE FUNCTION datetime_day (datetime ) RETURNS float8 AS 'select
> > date_part(''day'', $1)' LANGUAGE 'SQL';
> > INSERT INTO timelog values ('abc','varna','Thu 01 Jan 23:23:34 1998 EET','@ 10
> > mins');
> > INSERT INTO timelog values ('abc','sofia','Thu 01 Jan 23:23:34 1998 EET','@ 2
> > hours 10 mins');
> >
> > $ psql test
> > Welcome to the POSTGRESQL interactive sql monitor:
> >   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> >
> >    type \? for help on slash commands
> >    type \q to quit
> >    type \g or terminate with semicolon to execute query
> >  You are currently connected to the database: test
> >
> > test=> create index timelog_date_idx on timelog (datetime_day(start_time));
> > ERROR:  DefineIndex: (null) class not found
> > test=>
> >
> > Also, using date_part() directly in the index definition:
> >
> > test=> create index timelog_date_idx on timelog (datetime_part('day',
> > start_time));
> > ERROR:  parser: parse error at or near "'"
> >
> >
> > Can something be done about this before 6.4 is released?
> >
> > Daniel
> >
> >
> >
> >

    Problem 1:
        You must specify an operator class for the index.

    Solution:
        CREATE INDEX timelog_date_idx ON timelog
            (datetime_day(start_time) float8_ops);

        in the above case.

    Problem 2:
        Only  tuple  attributes  can  be  given  to  a functional
        indexes proc. This is  why  date_part('day',  start_time)
        cannot be used directly. There is no place in pg_index to
        store constant values.

    Solution:
        As you did with a wrapper function. But...

    Problem 3:
        You cannot use  SQL  language  functions  for  functional
        index    procs.     The   index   access   methods   call
        fmgr_array_args() to compute the functional index  value.
    And this cannot execute SQL language functions.

    Solution:
        Write  a  C  language function for it or use the PL/pgSQL
        function

        CREATE FUNCTION datetime_day(datetime) RETURNS float8 AS '
            BEGIN
                RETURN date_part(''day'', $1);
            END;'
            LANGUAGE 'plpgsql';

        after  installing  the  procedural   language   in   your
        database.  Did I tell that I like PL/pgSQL more and more?
    Think it was really time for it.

    Problem 4:
        The  isNull  flag used in access/index/indexam.c function
        GetIndexValue() is not initialized correctly.

    Solution:
        Apply the patch at the end.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #


*** indexam.c.orig    Fri Oct  2 15:16:49 1998
--- indexam.c    Fri Oct  2 15:17:24 1998
***************
*** 362,368 ****
                bool *attNull)
  {
      Datum        returnVal;
!     bool        isNull;

      if (PointerIsValid(fInfo) && FIgetProcOid(fInfo) != InvalidOid)
      {
--- 362,368 ----
                bool *attNull)
  {
      Datum        returnVal;
!     bool        isNull = FALSE;

      if (PointerIsValid(fInfo) && FIgetProcOid(fInfo) != InvalidOid)
      {
***************
*** 375,387 ****
                                        attrNums[i],
                                        hTupDesc,
                                        attNull);
          }
          returnVal = (Datum) fmgr_array_args(FIgetProcOid(fInfo),
                                              FIgetnArgs(fInfo),
                                              (char **) attData,
                                              &isNull);
          pfree(attData);
!         *attNull = FALSE;
      }
      else
          returnVal = heap_getattr(tuple, attrNums[attOff], hTupDesc, attNull);
--- 375,389 ----
                                        attrNums[i],
                                        hTupDesc,
                                        attNull);
+             if (*attNull)
+                 isNull = TRUE;
          }
          returnVal = (Datum) fmgr_array_args(FIgetProcOid(fInfo),
                                              FIgetnArgs(fInfo),
                                              (char **) attData,
                                              &isNull);
          pfree(attData);
!         *attNull = isNull;
      }
      else
          returnVal = heap_getattr(tuple, attrNums[attOff], hTupDesc, attNull);