Обсуждение: functional indexes

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

Added to open 6.4 bugs 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
>
>
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

>
> 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);