Thread: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

Hello everybody,

I'd like to implement a btree date index from json input data.

>>># \d docs
                         Table "public.docs"
 Column |  Type   |                     Modifiers                    
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('docs_id_seq'::regclass)
 meta   | jsonb   |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)

So, I did:

>>># create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::date));
ERROR:  functions in index expression must be marked IMMUTABLE

Searching the Internet for a solution, I tried several variants of this:

>>># create index docs_birthdate_idx ON docs using btree ((to_timestamp(meta->>'birthdate', 'YYYY-MM-DD') at time zone 'UTC'));
ERROR:  functions in index expression must be marked IMMUTABLE

Years ago, I circumvented it by creating an immutable function. This, though, just hides the errors since I would use the mutable expression anyway and mark it as immutable.


So, what is the problem here?


Regards,
Sven

On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
> <mailto:srkunze@mail.de>>wrote:
>
>     >>># create index docs_birthdate_idx ON docs using btree
>     (((meta->>'birthdate')::date));
>     ERROR:  functions in index expression must be marked IMMUTABLE
>
>     So, what is the problem here?
>
>
> ​Date functions are inherently not immutable because of timezones. Your
> solution of using to_timestamp doesn't help because it automatically
> returns a value in WITH TIMESTAMP. Do you get anywhere by using
> "::timestamp without time zone" instead, as suggested here?

My attempts at working the OP's problem passed through that:

test=> create index docs_birthdate_idx ON docs using btree (((meta->>'birthdate')::timestamp));
ERROR:  functions in index expression must be marked IMMUTABLE

This works:

test=> create index docs_birthdate_idx ON docs using btree ((meta->>'birthdate'));
CREATE INDEX

It is the act of casting that fails. Other then the OP's own suggestion of creating
a function that wraps the operation and marks it immutable I don't have a solution at
this time.

>
> https://www.postgresql.org/message-id/4E039D16.20704%40pinpointresearch.com
>
> Geoff
>
> ​


--
Adrian Klaver
adrian.klaver@aklaver.com

Geoff Winkless <pgsqladmin@geoff.dj> writes:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>>> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
>>> <mailto:srkunze@mail.de>>wrote:
>>>> # create index docs_birthdate_idx ON docs using btree
>>>> (((meta->>'birthdate')::date));
>>>> ERROR:  functions in index expression must be marked IMMUTABLE

>>> ​Date functions are inherently not immutable because of timezones.

> ​ Isn't the point that casting to ::timestamp will still keep the
> timezone?  Hence casting to "without timezone".

There are multiple reasons why the text-to-datetime conversion functions
are not immutable:

* some of them depend on the current timezone (but I don't believe date_in
does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.

I'm not entirely sure why the OP feels he needs an index on this
expression.  If he's willing to restrict the column to have the
exact format 'YYYY-MM-DD', then a regular textual index would sort
the same anyway.  Perhaps what's needed is just to add a CHECK
constraint verifying that the column has that format.

            regards, tom lane

On 02/26/2017 08:15 AM, Geoff Winkless wrote:
> On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>wrote:
>
>     On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>     > On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de <mailto:srkunze@mail.de>
>     > <mailto:srkunze@mail.de <mailto:srkunze@mail.de>>>wrote:
>     >
>     >     >>># create index docs_birthdate_idx ON docs using btree
>     >     (((meta->>'birthdate')::date));
>     >     ERROR:  functions in index expression must be marked IMMUTABLE
>     >
>     >     So, what is the problem here?
>     >
>     >
>     > ​Date functions are inherently not immutable because of timezones. Your
>     > solution of using to_timestamp doesn't help because it automatically
>     > returns a value in WITH TIMESTAMP. Do you get anywhere by using
>     > "::timestamp without time zone" instead, as suggested here?
>
> ​Of course I meant "WITH TIMEZONE" here, finger slippage.

That does not work either:

test=> create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::timestamptz));
ERROR:  functions in index expression must be marked IMMUTABLE


> ​
>
>     My attempts at working the OP's problem passed through that:
>
> ​​Apologies, I don't have that reply in the thread in my mailbox.

No apologies needed I had not posted my attempts at that point. It was
more me thinking out loud.

> ​
>
>     test=> create index docs_birthdate_idx ON docs using btree
>     (((meta->>'birthdate')::timestamp));
>     ERROR:  functions in index expression must be marked IMMUTABLE
>
>
> ​ Isn't the point that casting to ::timestamp will still keep the
> timezone?  Hence casting to "without timezone".
>
>     This works:
>
>     test=> create index docs_birthdate_idx ON docs using btree
>     ((meta->>'birthdate'));
>     CREATE INDEX
>
>     It is the act of casting that fails. Other then the OP's own
>     suggestion of creating
>     a function that wraps the operation and marks it immutable I don't
>     have a solution at
>     this time
>
>
> ​I can imagine that without a cast, depending on the way birthdate is
> stored, it may behave differently to a cast index for ordering.
>
> Geoff


--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/26/2017 08:50 AM, Tom Lane wrote:
> Geoff Winkless <pgsqladmin@geoff.dj> writes:
>> On 26 February 2017 at 16:09, Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>>> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
>>>> On 26 February 2017 at 10:09, Sven R. Kunze <srkunze@mail.de
>>>> <mailto:srkunze@mail.de>>wrote:
>>>>> # create index docs_birthdate_idx ON docs using btree
>>>>> (((meta->>'birthdate')::date));
>>>>> ERROR:  functions in index expression must be marked IMMUTABLE
>
>>>> ​Date functions are inherently not immutable because of timezones.
>
>> ​ Isn't the point that casting to ::timestamp will still keep the
>> timezone?  Hence casting to "without timezone".
>
> There are multiple reasons why the text-to-datetime conversion functions
> are not immutable:
>
> * some of them depend on the current timezone (but I don't believe date_in
> does);
>
> * all of them depend on the current datestyle setting, eg to resolve
> '02/03/2017';
>
> * all of them accept strings with time-varying values, such as 'now'
> or 'today'.
>
> You could get around the second and third points with to_timestamp(),
> but since the only variant of that is one that yields timestamptz and
> hence is affected by the timezone setting, it's still not immutable.
>
> I'm not entirely sure why the OP feels he needs an index on this
> expression.  If he's willing to restrict the column to have the
> exact format 'YYYY-MM-DD', then a regular textual index would sort
> the same anyway.  Perhaps what's needed is just to add a CHECK
> constraint verifying that the column has that format.

The OP is trying to create an index on the value of a jsonb key. Would
the above still apply or am I misunderstanding the reference to column?

or

The below works:

test=> create index docs_birthdate_idx ON docs using btree
((meta->>'birthdate'));
CREATE INDEX

So if the text values of 'birthdate' are consistent the index would work
without the cast?

>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 02/26/2017 08:50 AM, Tom Lane wrote:
>> I'm not entirely sure why the OP feels he needs an index on this
>> expression.  If he's willing to restrict the column to have the
>> exact format 'YYYY-MM-DD', then a regular textual index would sort
>> the same anyway.  Perhaps what's needed is just to add a CHECK
>> constraint verifying that the column has that format.

> The OP is trying to create an index on the value of a jsonb key. Would
> the above still apply or am I misunderstanding the reference to column?

Sure, I was using "column" loosely to refer to the meta->>'birthdate'
expression.

> The below works:
> test=> create index docs_birthdate_idx ON docs using btree
> ((meta->>'birthdate'));
> CREATE INDEX
> So if the text values of 'birthdate' are consistent the index would work
> without the cast?

Yeah, seems to me you could do things like
    ... WHERE meta->>'birthdate' > '2017-02-26'
and it would Just Work, though I'd admit there's a deficiency of sanity
checking for the RHS constant in this example.

            regards, tom lane

On 02/26/2017 09:42 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 02/26/2017 08:50 AM, Tom Lane wrote:
>>> I'm not entirely sure why the OP feels he needs an index on this
>>> expression.  If he's willing to restrict the column to have the
>>> exact format 'YYYY-MM-DD', then a regular textual index would sort
>>> the same anyway.  Perhaps what's needed is just to add a CHECK
>>> constraint verifying that the column has that format.
>
>> The OP is trying to create an index on the value of a jsonb key. Would
>> the above still apply or am I misunderstanding the reference to column?
>
> Sure, I was using "column" loosely to refer to the meta->>'birthdate'
> expression.

Alright, thanks. It is just that with array/hstore/json(b) I see a table
in a column in a table and I need to be clear in my mind what is being
referred to.

>
>> The below works:
>> test=> create index docs_birthdate_idx ON docs using btree
>> ((meta->>'birthdate'));
>> CREATE INDEX
>> So if the text values of 'birthdate' are consistent the index would work
>> without the cast?
>
> Yeah, seems to me you could do things like
>     ... WHERE meta->>'birthdate' > '2017-02-26'
> and it would Just Work, though I'd admit there's a deficiency of sanity
> checking for the RHS constant in this example.
>
>             regards, tom lane
>


--
Adrian Klaver
adrian.klaver@aklaver.com

There are multiple reasons why the text-to-datetime conversion functions
are not immutable:

* some of them depend on the current timezone (but I don't believe date_in
does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.

I understand that timezone settings can have serious consequences when parsing text to datetime.

My conceptual issue is that wrapping an "unsafe" operation up into a function and **marking** it as "safe" is not making things safer. Basically by-passing security guards.


So, what can I do to parse texts to date(times) in a safe manner?


I'd like to do it the right way. I can safely provide the timezone for those dates but it won't be in the jsonb data.

I'm not entirely sure why the OP feels he needs an index on this
expression.  If he's willing to restrict the column to have the
exact format 'YYYY-MM-DD', then a regular textual index would sort
the same anyway.  Perhaps what's needed is just to add a CHECK
constraint verifying that the column has that format.

These were my reasons:

1) sanity checks (already noted)
2) index date ranges (using gist)
3) maybe performance (comparing texts vs comparing dates) but I couldn't think of ways to test this



That's the current schema:
                         Table "public.docs"
 Column |  Type   |                     Modifiers                    
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('docs_id_seq'::regclass)
 meta   | jsonb   |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)
    "docs_address_idx" gin (to_tsvector('english'::regconfig, meta ->> 'address'::text))
    "docs_address_trgm_idx" gin ((meta ->> 'address'::text) gin_trgm_ops)
    "docs_birthdate_idx" btree ((meta ->> 'birthdate'::text))
    "docs_meta_idx" gin (meta jsonb_path_ops)
    "docs_name_idx" gin (to_tsvector('english'::regconfig, meta ->> 'name'::text))


Thanks to the ISO date format, I got by with a btree index on birthdate as Tom suggested.


The index supports queries like the following (although 22secs still is not great on 10M rows)

explain analyze select meta->>'birthdate' from docs where meta->>'birthdate' > '2000-01-01' and meta->>'birthdate' < '2000-12-31' order by meta->>'birthdate';
                                                               QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using docs_birthdate_idx on docs  (cost=0.43..46067.43 rows=50000 width=136) (actual time=2.118..22177.710 rows=209955 loops=1)
   Index Cond: (((meta ->> 'birthdate'::text) > '2000-01-01'::text) AND ((meta ->> 'birthdate'::text) < '2000-12-31'::text))
 Planning time: 0.205 ms
 Execution time: 22229.615 ms


Regard,
Sven

On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de> wrote:

So, what can I do to parse texts to date(times) in a safe manner?

You know best the format of your data; if you know that your date field is always in a particular style and timezone, you can write a function that can be considered safe to set IMMUTABLE, where a more generic​ system todate function cannot.

It might be sensible to call the function something that describes it exactly, rather than my_to_date you could call it utc_yyyymmdd_todate or something, just in case someone comes along later and sees an immutable todate function and thinks they can use it for something else.

Geoff

Thanks, Geoff. It's not the most convenient way to define an index to define a function first and that all future queries need to use that very function in order to utilize the index. Though, it's the most safest and best documenting way.


So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are timezone-influenced.

I still feel that a function is overkill for a simple text to date conversion. Couldn't there be an IMMUTABLE modifier for an expression to mark it as immutable?


"SELECT '2007-02-02'::date;" just works. It would be great if one could define an index with the same ease. I already can see how our application developers need constant reminders that "in case of dates, use 'magic_function' first". If they don't, the application will suffer from bad performance.


Thanks in advance for your replies.

Regards,
Sven

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
> On 27.02.2017 12:10, Geoff Winkless wrote:
>> On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@mail.de
>> <mailto:srkunze@mail.de>>wrote:
>>
>>
>>     So, what can I do to parse texts to date(times) in a safe manner?
>>
>>
>> You know best the format of your data; if you know that your date
>> field is always in a particular style and timezone, you can write a
>> function that can be considered safe to set IMMUTABLE, where a more
>> generic​ system todate function cannot.
>>
>> It might be sensible to call the function something that describes it
>> exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
>> or something, just in case someone comes along later and sees an
>> immutable todate function and thinks they can use it for something else.
>>
>> Geoff
>
> Thanks, Geoff. It's not the most convenient way to define an index to
> define a function first and that all future queries need to use that
> very function in order to utilize the index. Though, it's the most
> safest and best documenting way.
>
>
> So, I got two additional questions:
>
> Why is this relevant for dates? I cannot see that dates are
> timezone-influenced.

Per Tom's post, see points 2 & 3:

"* some of them depend on the current timezone (but I don't believe
date_in does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
"

>
> I still feel that a function is overkill for a simple text to date
> conversion. Couldn't there be an IMMUTABLE modifier for an expression to
> mark it as immutable?
>
>
> "SELECT '2007-02-02'::date;" just works. It would be great if one could
> define an index with the same ease. I already can see how our
> application developers need constant reminders that "in case of dates,
> use 'magic_function' first". If they don't, the application will suffer
> from bad performance.
>
>
> Thanks in advance for your replies.
>
> Regards,
> Sven


--
Adrian Klaver
adrian.klaver@aklaver.com

On 27.02.2017 16:37, Adrian Klaver wrote:
> On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
>> Why is this relevant for dates? I cannot see that dates are
>> timezone-influenced.
>
> Per Tom's post, see points 2 & 3:

Maybe, I am on a completely wrong track here, but to me dates still
don't look timezone dependent. They are just dates and not times, aren't
they?

> "* some of them depend on the current timezone (but I don't believe
> date_in does);
>
> * all of them depend on the current datestyle setting, eg to resolve
> '02/03/2017';
>
> * all of them accept strings with time-varying values, such as 'now'
> or 'today'.
>
> You could get around the second and third points with to_timestamp(),
> but since the only variant of that is one that yields timestamptz and
> hence is affected by the timezone setting, it's still not immutable.
> "

Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp
and to_date with a third parameter to specify the otherwise
setting-dependent timezone?

>> I still feel that a function is overkill for a simple text to date
>> conversion. Couldn't there be an IMMUTABLE modifier for an expression to
>> mark it as immutable?

Any thoughts on this?


>> "SELECT '2007-02-02'::date;" just works. It would be great if one could
>> define an index with the same ease. I already can see how our
>> application developers need constant reminders that "in case of dates,
>> use 'magic_function' first". If they don't, the application will suffer
>> from bad performance.

Best regards,
Sven

On 02/27/2017 09:08 AM, Sven R. Kunze wrote:
> On 27.02.2017 16:37, Adrian Klaver wrote:
>> On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
>>> Why is this relevant for dates? I cannot see that dates are
>>> timezone-influenced.
>>
>> Per Tom's post, see points 2 & 3:
>
> Maybe, I am on a completely wrong track here, but to me dates still
> don't look timezone dependent. They are just dates and not times, aren't
> they?

Yes, but is not about timezone dependency, it is about the other
dependencies listed in the second and third points. Namely the datestyle
setting and magic strings e.g. 'now'

>
>> "* some of them depend on the current timezone (but I don't believe
>> date_in does);
>>
>> * all of them depend on the current datestyle setting, eg to resolve
>> '02/03/2017';
>>
>> * all of them accept strings with time-varying values, such as 'now'
>> or 'today'.
>>
>> You could get around the second and third points with to_timestamp(),
>> but since the only variant of that is one that yields timestamptz and
>> hence is affected by the timezone setting, it's still not immutable.
>> "
>
> Reading this through again, I got an idea:
>
> Wouldn't it be possible to provide an immutable variant of to_timestamp
> and to_date with a third parameter to specify the otherwise
> setting-dependent timezone?
>
>>> I still feel that a function is overkill for a simple text to date
>>> conversion. Couldn't there be an IMMUTABLE modifier for an expression to
>>> mark it as immutable?
>
> Any thoughts on this?
>
>
>>> "SELECT '2007-02-02'::date;" just works. It would be great if one could
>>> define an index with the same ease. I already can see how our
>>> application developers need constant reminders that "in case of dates,
>>> use 'magic_function' first". If they don't, the application will suffer
>>> from bad performance.
>
> Best regards,
> Sven
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Yes, but is not about timezone dependency, it is about the other dependencies listed in the second and third points. Namely the datestyle setting and magic strings e.g. 'now'

I am sorry, I still don't understand. to_date and to_timestamp require datestyle settings per se and magic strings don't work.


=# -- required datestyle
=# select to_date('2000-01-01');
ERROR:  function to_date(unknown) does not exist
LINE 1: select to_date('2000-01-01');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


=# -- magic strings don't work
=# select to_date('');
ERROR:  invalid value "epoc" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('epoch', 'YYYY-MM-DD');
ERROR:  invalid value "epoc" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('infinity', 'YYYY-MM-DD');
ERROR:  invalid value "infi" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('-infinity', 'YYYY-MM-DD');
ERROR:  invalid value "-inf" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('now', 'YYYY-MM-DD');
ERROR:  invalid value "now" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('today', 'YYYY-MM-DD');
ERROR:  invalid value "toda" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('tomorrow', 'YYYY-MM-DD');
ERROR:  invalid value "tomo" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('yesterday', 'YYYY-MM-DD');
ERROR:  invalid value "yest" for "YYYY"
DETAIL:  Value must be an integer.
=# select to_date('allballs', 'YYYY-MM-DD');
ERROR:  invalid value "allb" for "YYYY"
DETAIL:  Value must be an integer.

Regards,
Sven

On 02/28/2017 01:35 AM, Sven R. Kunze wrote:
> On 27.02.2017 18:17, Adrian Klaver wrote:
>> Yes, but is not about timezone dependency, it is about the other
>> dependencies listed in the second and third points. Namely the
>> datestyle setting and magic strings e.g. 'now'
>
> I am sorry, I still don't understand. to_date and to_timestamp require
> datestyle settings per se and magic strings don't work.

See here:

https://www.postgresql.org/message-id/11190.1488127834%40sss.pgh.pa.us

"There are multiple reasons why the text-to-datetime conversion
functions are not immutable"

Tom was referring to the text --> date cast you where attempting in your
original index definition:

create index docs_birthdate_idx ON docs using btree
(((meta->>'birthdate')::date));

So:

test=> select 'today'::date;
     date
------------
  2017-02-28
(1 row)

test=> select 'now'::date;
     date
------------
  2017-02-28
(1 row)

test=> set datestyle = 'SQL, DMY';
SET
test=> select 'today'::date;
     date
------------
  28/02/2017
(1 row)

test=> select 'now'::date;
     date
------------
  28/02/2017
(1 row)


Now you tried to work around the casting issue by using to_timestamp:

create index docs_birthdate_idx ON docs using btree
((to_timestamp(meta->>'birthdate', 'YYYY-MM-DD') at time zone 'UTC'));

but that introduced the issue that to_timestamp returns a timestamptz
and so you end up with a dependency on timezones.

>
>
> =# -- required datestyle
> =# select to_date('2000-01-01');
> ERROR:  function to_date(unknown) does not exist
> LINE 1: select to_date('2000-01-01');
>                ^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
> =# -- magic strings don't work
> =# select to_date('');
> ERROR:  invalid value "epoc" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('epoch', 'YYYY-MM-DD');
> ERROR:  invalid value "epoc" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('infinity', 'YYYY-MM-DD');
> ERROR:  invalid value "infi" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('-infinity', 'YYYY-MM-DD');
> ERROR:  invalid value "-inf" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('now', 'YYYY-MM-DD');
> ERROR:  invalid value "now" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('today', 'YYYY-MM-DD');
> ERROR:  invalid value "toda" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('tomorrow', 'YYYY-MM-DD');
> ERROR:  invalid value "tomo" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('yesterday', 'YYYY-MM-DD');
> ERROR:  invalid value "yest" for "YYYY"
> DETAIL:  Value must be an integer.
> =# select to_date('allballs', 'YYYY-MM-DD');
> ERROR:  invalid value "allb" for "YYYY"
> DETAIL:  Value must be an integer.
>
> Regards,
> Sven


--
Adrian Klaver
adrian.klaver@aklaver.com

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?

Regards,
Sven

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:


test=> select * from pg_proc where proname ilike 'to_date';
...

provolatile     | s

....


https://www.postgresql.org/docs/9.6/static/catalog-pg-proc.html

provolatile     char

provolatile tells whether the function's result depends only on its
input arguments, or is affected by outside factors. It is i for
"immutable" functions, which always deliver the same result for the same
inputs. It is s for "stable" functions, whose results (for fixed inputs)
do not change within a scan. It is v for "volatile" functions, whose
results might change at any time. (Use v also for functions with
side-effects, so that calls to them cannot get optimized away.)


>
> Regards,
> Sven
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

​That would seem to be it.

cache_locale_time() at the top of DCH_to_char which is in the call stack of the shared parsing code for both to_date and to_timestamp.

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

Supposedly one could provide a version of to_date that accepts a locale in which to interpret names in the input data - or extend the format string with some kind of "{locale=en_US}" syntax to avoid changing the function signature.

David J.

I don't know if this is the right way and list to ask for this:

But if this is acceptable, I would be willing to contribute a patch to enable exactly this.


Regards,
Sven

On 1 March 2017 at 13:36, Sven R. Kunze <srkunze@mail.de> wrote:

On 28.02.2017 17:50, David G. Johnston wrote:

Supposedly one could provide a version of to_date that accepts a locale in which to interpret names in the input data - or extend the format string with some kind of "{locale=en_US}" syntax to avoid changing the function signature.

if this is acceptable, I would be willing to contribute a patch to enable exactly this.

​I don't see how that would help. You can't set a function to be immutable for "some" inputs (ie where locale is specified in the format string).

The only way to do it would be to add to_date(string, string, string) where the third string specifies the locale, at which point I don't really see why you would gain anything over creating your own UDF.

I don't consider rolling an UDF the best alternative especially after having looked through many solution proposals on the Web which just take an mutable expression and wrap them up in an immutable function.


An additional variant of to_date/to_timestamp would have the following advantages (point 2 is most important):

1) a single, recommended and easy way to parse date(times)
2) make people aware of the locale/timezone issue but give them a standard tool to solve it
3) make all those and related Google entries (https://www.google.de/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=postgresql+to_date+immutable&*) point to the same and safe solution eventually

Sven

One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for May), although you would have to​ do some more intense research to be sure. As far as I can see there's no other reason why to_date would need to be marked as stable/volatile, is there?

I don't think so. It could be viable.

On the down side I imagine it would involve some potentially-prohibitively-large lookup tables; it would also end up with a technical incompatibility in that what ANSI SQL would reject as not-a-date might be parsed as a date.

There is another issue: languages change (admittedly very slowly) but I wouldn't want PostgreSQL to be incompatible with future generations.
Your performance argument weighs heavier, though.

I'm not in a position to judge if either of those would be acceptable.

Do you think I should post to pgsql-hackers?


Sven

​One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for May), although you would have to​ do some more intense research to be sure. As far as I can see there's no other reason why to_date would need to be marked as stable/volatile, is there?

As a side-note, it seems there are overlapping short months:


Using
this webpage http://web.library.yale.edu/cataloging/months.htm
this JS(Firefox) JSON.stringify([for (x of document.querySelectorAll('table.grid tr td')) x.textContent])
this Python

import pprint
from collections import defaultdict

def chunks(l, n):
    """Yield successive n-sized chunks from l."""
    for i in range(0, len(l), n):
        yield l[i:i + n]

a=<json output from Firefox>
a=list(chunks(a, 13))

dd=defaultdict(list)
for l in a:
     for i, m in enumerate(l):
         if i == 0:
             continue
         dd[m].append((i, l[0]))

pprint.pprint(dict(dd))
{'\nmart\n': [(3, 'Serbian')],
 'Ag.': [(8, 'Indonesian')],
 'Agustos': [(8, 'Turkish')],
 'Apr': [(4, 'Latin')],
 'Apr.': [(4, 'English'),
          (4, 'German'),
          (4, 'Greek, Modern'),
          (4, 'Indonesian'),
          (4, 'Malaysian'),
          (4, 'Romanian')],
 'Aralik': [(12, 'Turkish')],
 'Aug.': [(8, 'English'),
          (8, 'German'),
          (8, 'Greek, Modern'),
          (8, 'Latin'),
          (8, 'Romanian')],
 'Awst': [(8, 'Welsh')],
 'Chwef.': [(2, 'Welsh')],
 'Dec.': [(12, 'English'), (12, 'Latin'), (12, 'Romanian')],
 'Dek.': [(12, 'Greek, Modern')],
 'Des.': [(12, 'Indonesian')],
 'Dez.': [(12, 'German')],
 'Dis.': [(12, 'Malaysian')],
 'Ebr.': [(4, 'Welsh')],
 'Ekim': [(10, 'Turkish')],
 'Eylul': [(9, 'Turkish')],
 'Feb.': [(2, 'English'), (2, 'German'), (2, 'Malaysian'), (2, 'Romanian')],
 'Febr.': [(2, 'Latin')],
 'Gorff.': [(7, 'Welsh')],
 'Haziran': [(6, 'Turkish')],
 'Hyd.': [(10, 'Welsh')],
 'Ian.': [(1, 'Greek, Modern'), (1, 'Latin'), (1, 'Romanian')],
 'Ion.': [(1, 'Welsh')],
 'Ioul.': [(7, 'Greek, Modern')],
 'Ioun.': [(6, 'Greek, Modern')],
 'Iul.': [(7, 'Latin')],
 'Iulie': [(7, 'Romanian')],
 'Iun.': [(6, 'Latin')],
 'Iunie': [(6, 'Romanian')],
 'Jan.': [(1, 'English'), (1, 'Malaysian')],
 'Jan./Djan.\xc2\xa0': [(1, 'Indonesian')],
 'Jan./J\xc3\xa4n.': [(1, 'German')],
 'Julai': [(7, 'Malaysian')],
 'Juli': [(7, 'German')],
 'Juli/Djuli': [(7, 'Indonesian')],
 'July': [(7, 'English')],
 'Jun': [(6, 'Malaysian')],
 'June': [(6, 'English')],
 'Juni': [(6, 'German')],
 'Juni/Djuni': [(6, 'Indonesian')],
 'Kasim': [(11, 'Turkish')],
 'Mac': [(3, 'Malaysian')],
 'Mai': [(5, 'German'), (5, 'Romanian'), (5, 'Welsh')],
 'Mai.': [(5, 'Latin')],
 'Maios': [(5, 'Greek, Modern')],
 'Mar.': [(3, 'English'), (3, 'Romanian')],
 'Mart.': [(3, 'Greek, Modern'), (3, 'Latin')],
 'Maw.': [(3, 'Welsh')],
 'May': [(5, 'English')],
 'Mayis': [(5, 'Turkish')],
 'Medi': [(9, 'Welsh')],
 'Meh.': [(6, 'Welsh')],
 'Mei': [(5, 'Malaysian')],
 'Mei/Mai': [(5, 'Indonesian')],
 'Mrt.': [(3, 'Indonesian')],
 'M\xc3\xa4rz': [(3, 'German')],
 'Nisan': [(4, 'Turkish')],
 'Noem.': [(11, 'Greek, Modern')],
 'Noiem.': [(11, 'Romanian')],
 'Nop.': [(11, 'Indonesian')],
 'Nov.': [(11, 'English'), (11, 'German'), (11, 'Latin'), (11, 'Malaysian')],
 'Ocak': [(1, 'Turkish')],
 'Oct.': [(10, 'English'), (10, 'Latin'), (10, 'Romanian')],
 'Og': [(8, 'Malaysian')],
 'Okt.': [(10, 'German'),
          (10, 'Greek, Modern'),
          (10, 'Indonesian'),
          (10, 'Malaysian')],
 'Peb.': [(2, 'Indonesian')],
 'Phevr.': [(2, 'Greek, Modern')],
 'Rhag.': [(12, 'Welsh')],
 'Saus.': [(1, 'Lithuanian')],
 'Sept.': [(9, 'English'),
           (9, 'German'),
           (9, 'Greek, Modern'),
           (9, 'Indonesian'),
           (9, 'Latin'),
           (9, 'Malaysian'),
           (9, 'Romanian')],
 'Subat': [(2, 'Turkish')],
 'Tach.': [(11, 'Welsh')],
 'Temmuz': [(7, 'Turkish')],
 'abr.': [(4, 'Spanish')],
 'abril': [(4, 'Portuguese')],
 'ag.': [(8, 'Italian')],
 'agosto': [(8, 'Portuguese'), (8, 'Spanish')],
 'ao\xc3\xbbt': [(8, 'French')],
 'apr.': [(4, 'Dutch'),
          (4, 'Estonian'),
          (4, 'Italian'),
          (4, 'Latvian'),
          (4, 'Russian')],
 'apr./mali traven': [(4, 'Slovenian')],
 'april': [(4, 'Bosnian'),
           (4, 'Bulgarian'),
           (4, 'Danish'),
           (4, 'Norwegian'),
           (4, 'Serbian'),
           (4, 'Swedish')],
 'aug.': [(8, 'Bosnian'),
          (8, 'Danish'),
          (8, 'Dutch'),
          (8, 'Estonian'),
          (8, 'Hungarian'),
          (8, 'Latvian'),
          (8, 'Norwegian'),
          (8, 'Swedish')],
 'avg.': [(8, 'Bulgarian'), (8, 'Russian'), (8, 'Serbian')],
 'avg./veliki\xc2\xa0srpan': [(8, 'Slovenian')],
 'avril': [(4, 'French')],
 'bal.': [(4, 'Lithuanian')],
 'ber.': [(3, 'Ukranian')],
 'birz': [(6, 'Lithuanian')],
 'brez.': [(3, 'Czech')],
 'brez./mar.': [(3, 'Slovak')],
 'cerv.': [(6, 'Czech')],
 'cerv./j\xc3\xban': [(6, 'Slovak')],
 'cerven.': [(7, 'Czech')],
 'cerven./j\xc3\xbal': [(7, 'Slovak')],
 'cher.': [(6, 'Ukranian')],
 'cherv.': [(6, 'Belorusian')],
 'czerw.': [(6, 'Polish')],
 'dec.': [(12, 'Danish'),
          (12, 'Dutch'),
          (12, 'Hungarian'),
          (12, 'Latvian'),
          (12, 'Serbian'),
          (12, 'Swedish')],
 'dec./gr.': [(12, 'Slovenian')],
 'dec.\xc2\xa0': [(12, 'Bosnian')],
 'dek.': [(12, 'Bulgarian'), (12, 'Russian')],
 'des.': [(12, 'Norwegian')],
 'dets.': [(12, 'Estonian')],
 'dez.': [(12, 'Portuguese')],
 'dic.': [(12, 'Italian'), (12, 'Spanish')],
 'dub.': [(4, 'Czech')],
 'dub./apr.': [(4, 'Slovak')],
 'd\xc3\xa9c.': [(12, 'French')],
 'enero': [(1, 'Spanish')],
 'feb.': [(2, 'Bosnian'),
          (2, 'Dutch'),
          (2, 'Hungarian'),
          (2, 'Latvian'),
          (2, 'Spanish')],
 'feb./svec.': [(2, 'Slovenian')],
 'febbr.': [(2, 'Italian')],
 'febr.': [(2, 'Danish'), (2, 'Norwegian'), (2, 'Serbian'), (2, 'Swedish')],
 'fev.': [(2, 'Portuguese')],
 'fevr.': [(2, 'Bulgarian'), (2, 'Russian')],
 'f\xc3\xa9vr.': [(2, 'French')],
 'geg.': [(5, 'Lithuanian')],
 'genn.': [(1, 'Italian')],
 'giugno': [(6, 'Italian')],
 'gr.': [(12, 'Lithuanian')],
 'grudz.': [(12, 'Polish')],
 'hrud.': [(12, 'Ukranian')],
 'ian.': [(1, 'Bulgarian')],
 'ianv.': [(1, 'Russian')],
 "iiul'": [(7, 'Russian')],
 "iiun'": [(6, 'Russian')],
 'iuli': [(7, 'Bulgarian')],
 'iuni': [(6, 'Bulgarian')],
 'jaan.': [(1, 'Estonian')],
 'jan.': [(1, 'Bosnian'),
          (1, 'Danish'),
          (1, 'Dutch'),
          (1, 'Hungarian'),
          (1, 'Latvian'),
          (1, 'Norwegian'),
          (1, 'Portuguese'),
          (1, 'Serbian'),
          (1, 'Swedish')],
 'jan./pros.': [(1, 'Slovenian')],
 'janv.': [(1, 'French')],
 'juil.': [(7, 'French')],
 'juin': [(6, 'French')],
 'jul.': [(7, 'Hungarian'), (7, 'Spanish')],
 'jul./mali srpan': [(7, 'Slovenian')],
 'julho': [(7, 'Portuguese')],
 'juli': [(7, 'Bosnian'),
          (7, 'Danish'),
          (7, 'Dutch'),
          (7, 'Norwegian'),
          (7, 'Serbian'),
          (7, 'Swedish')],
 'julijs': [(7, 'Latvian')],
 'jun.': [(6, 'Hungarian'), (6, 'Spanish')],
 'jun./roz.': [(6, 'Slovenian')],
 'junho': [(6, 'Portuguese')],
 'juni': [(6, 'Bosnian'),
          (6, 'Danish'),
          (6, 'Dutch'),
          (6, 'Norwegian'),
          (6, 'Serbian'),
          (6, 'Swedish')],
 'junijs': [(6, 'Latvian')],
 'juuli': [(7, 'Estonian')],
 'juuni': [(6, 'Estonian')],
 'kastr.': [(10, 'Belorusian')],
 'kol.': [(8, 'Croatian')],
 'kovas': [(3, 'Lithuanian')],
 'kras.': [(4, 'Belorusian')],
 'kvet.': [(5, 'Czech')],
 'kvet/m\xc3\xa1j': [(5, 'Slovak')],
 'kvit.': [(4, 'Ukranian')],
 'kwiec.': [(4, 'Polish')],
 "l'ad./jan.": [(1, 'Slovak')],
 'lapkr.': [(11, 'Lithuanian')],
 'led.': [(1, 'Czech')],
 'liepa': [(7, 'Lithuanian')],
 'lip.': [(7, 'Belorusian'), (6, 'Croatian'), (7, 'Polish')],
 'list.': [(11, 'Belorusian'), (10, 'Croatian'), (11, 'Czech')],
 'list./nov.': [(11, 'Slovak')],
 'listop.': [(11, 'Polish')],
 'liut.': [(2, 'Belorusian'), (2, 'Ukranian')],
 'luglio': [(7, 'Italian')],
 'luty': [(2, 'Polish')],
 'lyp.': [(7, 'Ukranian')],
 'lyst.': [(11, 'Ukranian')],
 'maart': [(3, 'Dutch')],
 'magg.': [(5, 'Italian')],
 'mai': [(5, 'Bulgarian'),
         (5, 'Estonian'),
         (5, 'French'),
         (5, 'Norwegian'),
         (5, 'Russian')],
 'maijs': [(5, 'Latvian')],
 'maio': [(5, 'Portuguese')],
 'maj': [(5, 'Bosnian'),
         (5, 'Danish'),
         (5, 'Polish'),
         (5, 'Serbian'),
         (5, 'Swedish')],
 'maj./veliki traven': [(5, 'Slovenian')],
 'mar.': [(3, 'Italian'), (3, 'Polish')],
 'mar./sus.': [(3, 'Slovenian')],
 'mars': [(3, 'French'), (3, 'Norwegian'), (3, 'Swedish')],
 'mart': [(3, 'Bosnian'), (3, 'Bulgarian'), (3, 'Russian'), (3, 'Turkish')],
 'marts': [(3, 'Danish'), (3, 'Latvian')],
 'marzo': [(3, 'Spanish')],
 'mar\xc3\xa7o': [(3, 'Portuguese')],
 'mayo': [(5, 'Spanish')],
 'mei': [(5, 'Dutch')],
 'm\xc3\xa1j.': [(5, 'Hungarian')],
 'm\xc3\xa1rc.': [(3, 'Hungarian')],
 'm\xc3\xa4rts': [(3, 'Estonian')],
 'noem.': [(11, 'Bulgarian')],
 "noiabr'": [(11, 'Russian')],
 'nov.': [(11, 'Bosnian'),
          (11, 'Danish'),
          (11, 'Dutch'),
          (11, 'Estonian'),
          (11, 'French'),
          (11, 'Hungarian'),
          (11, 'Italian'),
          (11, 'Latvian'),
          (11, 'Norwegian'),
          (11, 'Portuguese'),
          (11, 'Serbian'),
          (11, 'Spanish'),
          (11, 'Swedish')],
 'nov./list.': [(11, 'Slovenian')],
 'oct.': [(10, 'French'), (10, 'Spanish')],
 'oct./okt.': [(10, 'Dutch')],
 'okt.': [(10, 'Bosnian'),
          (10, 'Bulgarian'),
          (10, 'Danish'),
          (10, 'Estonian'),
          (10, 'Hungarian'),
          (10, 'Latvian'),
          (10, 'Norwegian'),
          (10, 'Russian'),
          (10, 'Serbian'),
          (10, 'Swedish')],
 'okt./vino.': [(10, 'Slovenian')],
 'ott.': [(10, 'Italian')],
 'out.': [(10, 'Portuguese')],
 'ozuj.': [(3, 'Croatian')],
 'pazdz.': [(10, 'Polish')],
 'pros.': [(12, 'Croatian'), (12, 'Czech')],
 'pros./dec.': [(12, 'Slovak')],
 'rugp.': [(8, 'Lithuanian')],
 'rugs.': [(9, 'Lithuanian')],
 'ruj.': [(9, 'Croatian')],
 'ruj./okt.': [(10, 'Slovak')],
 'r\xc3\xadj.': [(10, 'Czech')],
 'sak./mar.': [(3, 'Belorusian')],
 'sent.': [(9, 'Russian')],
 'sept.': [(9, 'Bosnian'),
           (9, 'Bulgarian'),
           (9, 'Danish'),
           (9, 'Dutch'),
           (9, 'Estonian'),
           (9, 'French'),
           (9, 'Latvian'),
           (9, 'Norwegian'),
           (9, 'Serbian'),
           (9, 'Swedish')],
 'sept./kim.': [(9, 'Slovenian')],
 'sept./set.': [(9, 'Spanish')],
 'serp.': [(8, 'Ukranian')],
 'set.': [(9, 'Portuguese')],
 'sett.': [(9, 'Italian')],
 'sich.': [(1, 'Ukranian')],
 'sierp.': [(8, 'Polish')],
 'sijec.': [(1, 'Croatian')],
 'snezh.': [(12, 'Belorusian')],
 'spalis': [(10, 'Lithuanian')],
 'srp.': [(7, 'Croatian'), (8, 'Czech')],
 'srp./aug.': [(8, 'Slovak')],
 'stud.': [(11, 'Croatian')],
 'studz.': [(1, 'Belorusian')],
 'stycz.': [(1, 'Polish')],
 'svib.': [(5, 'Croatian')],
 'szept.': [(9, 'Hungarian')],
 'trav.': [(5, 'Belorusian'), (4, 'Croatian'), (5, 'Ukranian')],
 'vas.': [(2, 'Lithuanian')],
 'veebr.': [(2, 'Estonian')],
 'velj.': [(2, 'Croatian')],
 'ver.': [(9, 'Ukranian')],
 'veras.': [(9, 'Belorusian')],
 'wrzes.': [(9, 'Polish')],
 "zhniven'": [(8, 'Belorusian')],
 'zhovt.': [(10, 'Ukranian')],
 'z\xc3\xa1r.': [(9, 'Czech')],
 'z\xc3\xa1ri./sept.': [(9, 'Slovak')],
 '\xc3\xa1pr.': [(4, 'Hungarian')],
 '\xc3\xban.': [(2, 'Czech')],
 '\xc3\xban./feb.': [(2, 'Slovak')]}


Sven