trying to build immutable function in index

[Mark Steben <mark.steben@xxxxxxxxxxxxxxxxx>]

Good afternoon,

We are still running postgresql 9.4 - still trying to convince management to upgrade :<(

I am trying to create this index (the REGEXP portion is a date check)

create index custattr_value_includes on customer_attributes
   using btree (value, account_id, group_num, id)
 WHERE value::text ~ '^([0-9][0-9][0-9][0-9])-(([0]?[1-9])|([1][0-2]))-(([0-2]?[0-9])|([3][0-1]))$'::text
 AND value::text ~ '([0-9][0-9][0-9][0-9])-(0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01])|([0-9][0-9][0-9][0-9])-(0[469]|11])-(0[1-9]|[12][0-9]|30)|([0-9][0-9][0248][048]|[0-9][0-9][13579][26])-(02)-(0[1-9]|1[0-9]|2[0-9])|([0-9][0-9][0248][1235679]|[0-9][0-9][13579][01345789])-(02)-(0[1-9]|1[0-9]|2[0-8])'::text
AND value::date >= date_trunc('MONTH'::text, 'now'::text::date - '1 mon'::interval month)
AND value::date <= date_trunc('DAY'::text, 'now'::text::date::timestamp with time zone)  
 AND account_id IS NOT NULL

Predictably the date_trunc portion errors out with this error:

ERROR:  functions in index predicate must be marked IMMUTABLE

So I copy the source of the 3 flavors of date_trunc as follows:

CREATE OR REPLACE FUNCTION pg_catalog,date_trunc(
        text,
        timestamp with time zone,
        text)
    RETURNS timestamp with time zone
    LANGUAGE 'internal'

    COST 1
    STABLE STRICT PARALLEL SAFE
AS $BODY$timestamptz_trunc_zone$BODY$;

ALTER FUNCTION public.my_date_trunc(text, timestamp with time zone, text)
    OWNER TO postgres;

COMMENT ON FUNCTION public.my_date_trunc(text, timestamp with time zone, text)

changing the STABLE behavior to IMMUTABLE and the name from

pg_catalog.date_trunc to public.my_date_trunc (all three flavors) and recompile all three

   Then I change the index source to reference the newly compiled functions

  create index custattr_value_includes on customer_attributes
   using btree (value, account_id, group_num, id)
 WHERE value::text ~ '^([0-9][0-9][0-9][0-9])-(([0]?[1-9])|([1][0-2]))-(([0-2]?[0-9])|([3][0-1]))$'::text
 AND value::text ~ '([0-9][0-9][0-9][0-9])-(0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01])|([0-9][0-9][0-9][0-9])-(0[469]|11])-(0[1-9]|[12][0-9]|30)|([0-9][0-9][0248][048]|[0-9][0-9][13579][26])-(02)-(0[1-9]|1[0-9]|2[0-9])|([0-9][0-9][0248][1235679]|[0-9][0-9][13579][01345789])-(02)-(0[1-9]|1[0-9]|2[0-8])'::text
AND value::date >= my_date_trunc('MONTH'::text, 'now'::text::date - '1 mon'::interval month)
AND value::date <= my_date_trunc('DAY'::text, 'now'::text::date::timestamp with time zone)  
 AND account_id IS NOT NULL  

And I am still getting the error:  functions in index predicate must be marked IMMUTABLE 

Am I missing something?  Any comments welcome.  Thank you.

 

   

--

Mark Steben
 Database Administrator
@utoRevenue | Autobase 
  CRM division of Dominion Dealer Solutions 
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567

www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
 www.drivedominion.com