Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] functional indexes

From: jwieck(at)debis(dot)com (Jan Wieck)
To: maillist(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian)
Cc: daniel(at)digsys(dot)bg, pgsql-hackers(at)hub(dot)org
Subject: Re: [HACKERS] functional indexes
Date: 1998-10-02 13:36:09
Message-ID: m0zP5NR-000EBQC@orion.SAPserv.Hamburg.dsh.de (view raw or flat)
Thread:
Lists: pgsql-hackers
>
> 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(at)debis(dot)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);

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-10-02 14:09:37
Subject: Re: [HACKERS] Open 6.4 items
Previous:From: Matthew C. AycockDate: 1998-10-02 13:16:58
Subject: Re: [HACKERS] pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group