agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Larry Rosenman <ler@lerctr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: create index on a jsonb timestamp field?
Date: Sun, 19 May 2019 14:37:55 -0500
Message-ID: <8b5563be3bf7f0ae9826b223b61fa22d@lerctr.org> (raw)
In-Reply-To: <29956.1558228655@sss.pgh.pa.us>
References: <78a5d1232bb5ef5797a8ae6e1f23543f@lerctr.org>
<29956.1558228655@sss.pgh.pa.us>
On 05/18/2019 8:17 pm, Tom Lane wrote:
> Larry Rosenman <ler@lerctr.org> writes:
>> when I try to create an index on the query_time field of the json
>> structure I get:
>> ler=# create index dns_query_time_idx on dns_query(((data -> 'message'
>> ->> 'query_time')::text::timestamptz));
>> ERROR: functions in index expression must be marked IMMUTABLE
>
> Yeah, because the timestamptz input function has dependencies on
> both the datestyle and timezone GUCs. Given that your input is
> ISO-format with explicit time zone, you don't really care about
> either of those things, but the mutability check doesn't know that.
>
>> Is there any easy way to do this? Or, what would the experts
>> recommend
>> here?
>
> The sanest way to deal with this IMO is to make a column containing
> the extracted timestamp, which you could maintain with a trigger,
> and then index that. You could alternatively make a custom function
> that you (mis?)label as immutable, but your queries would have to
> use that same function in order to get matched to the index, so
> I dunno about that being a user-friendly approach.
This is what I wound up figuring out between my original post and yours,
and it works great.
>
> BTW, I'd had the idea that the GENERATED option in PG v13 would allow
> setting up this sort of case without bothering with a handwritten
> trigger,
> but it seems not:
>
> regression=# create table foo(data jsonb, ts timestamptz GENERATED
> ALWAYS AS ((data->>'ts')::timestamptz) stored);
> psql: ERROR: generation expression is not immutable
>
> I wonder if that's really necessary to insist on?
Good question. Is that something the project is going to look into?
>
> regards, tom lane
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: ler@lerctr.org
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106
view thread (5+ messages) latest in thread
Message-ID: <8b5563be3bf7f0ae9826b223b61fa22d@lerctr.org>
Permalink: ../8b5563be3bf7f0ae9826b223b61fa22d@lerctr.org/
Also on: postgresql.org/message-id/8b5563be3bf7f0ae9826b223b61fa22d@lerctr.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ler@lerctr.org, tgl@sss.pgh.pa.us
Subject: Re: create index on a jsonb timestamp field?
In-Reply-To: <8b5563be3bf7f0ae9826b223b61fa22d@lerctr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox