Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE - Mailing list pgsql-general

From David G. Johnston
Subject Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE
Date
Msg-id CAKFQuwaBuMJub1wLywG_WkSu57LT+h4AjdxMTSKfcaDM5_POLQ@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
On Tue, Feb 28, 2017 at 8:59 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/28/2017 07:30 AM, Sven R. Kunze wrote:
On 28.02.2017 15:40, Adrian Klaver wrote:
[explanation of why date casting and to_datetime don't work]

Why is to_date not immutable?

Not sure, but if I where to hazard a guess, from the source code in formatting.c:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068d2f8d776e35fef1179;hb=HEAD

to_date(PG_FUNCTION_ARGS)
{
        text       *date_txt = PG_GETARG_TEXT_P(0);
        text       *fmt = PG_GETARG_TEXT_P(1);
        DateADT         result;
        struct pg_tm tm;
        fsec_t          fsec;

        do_to_timestamp(date_txt, fmt, &tm, &fsec);
....

/*
 * do_to_timestamp: shared code for to_timestamp and to_date

The shared code makes it not immutable:

​Further on that reads:

"​* Parse the 'date_txt' according to 'fmt', return results as a struct pg_tm
 * and fractional seconds."

Which makes it sound like a pure text parsing routine that applies minimal logic to the values that it is parsing.  In fact, its doesn't even accept a TZ/OF formatting codes that could be used to determine shift.  to_date is always to going to output a date value that reflects the literal input text with "positions" determined by the input format code.

Per Tom Lane [1] while the current implementation is in fact immutable at some point in the future we may wish to add additional environmental logic which will require that it be marked STABLE.


I would be considering a trigger that populates a date column and a normal index on said date column.

David J.


pgsql-general by date:

Previous
From: Chris Withers
Date:
Subject: [GENERAL] json aggregation question
Next
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Querying JSON Lists