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