From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: trying to build immutable function in index |
Date: | 2020-06-02 22:57:14 |
Message-ID: | CAKFQuwYUKirSQgq7QvhdQLzCwaKyVf_jkOcPX0KUJ+2X-UK7vA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Tue, Jun 2, 2020 at 2:42 PM Mark Steben <mark(dot)steben(at)drivedominion(dot)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"
https://www.postgresql.org/docs/10/functions-matching.html#FUNCTIONS-POSIX-REGEXP
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$;
>
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 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?
>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | vinod kale | 2020-06-03 13:02:04 | Re: Block corruption Error. |
Previous Message | Rui DeSousa | 2020-06-02 22:24:14 | Re: trying to build immutable function in index |