Обсуждение: trying to build immutable function in index
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
--
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)
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
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
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
On Jun 2, 2020, at 5:42 PM, Mark Steben <mark.steben@drivedominion.com> wrote:AND value::date >= date_trunc('MONTH'::text, 'now'::text::date - '1 mon'::interval month)
I don’t think value::date is immutable; it will return a different date depending on your timezone.
Also this is also not immutable: 'now'::text::date - '1 mon'::interval month
You would have records indexed that should not be indexed the next month basically invalidating itself each month.
On Tue, Jun 2, 2020 at 2:42 PM Mark Steben <mark.steben@drivedominion.com> wrote:
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
Regular Expressions provide a nifty construct named "Quantifiers"
Predictably the date_trunc portion errors out with this error:ERROR: functions in index predicate must be marked IMMUTABLESo 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$;
That doesn't make any sense.
changing the STABLE behavior to IMMUTABLE
I don't see any behavior in the above, changed or otherwise.
and the name frompg_catalog.date_trunc to public.my_date_trunc (all three flavors) and recompile all threeThen I change the index source to reference the newly compiled functionscreate 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 NULLAnd I am still getting the error: functions in index predicate must be marked IMMUTABLEAm I missing something?
A self-contained example. And understanding that even though you place the "check" regex expression "before" the "value::date" ones there is no promise of ordering among the various AND clauses. You are trying to define a static constraint relative to "now" which is a fundamental violation of what a static constraint does. Time should seldom be a component of a constraint, variable time should never.
In short, you think what you are doing should work and you think the system is giving you a bogus error preventing you from doing so. When in fact you are going about the entire problem incorrectly. If this is really the general form that you require write a trigger.
David J.