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