create index on a jsonb timestamp field?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

create index on a jsonb timestamp field?

Larry Rosenman
I'm playing with DNSTAP (dnstap.info) data and loading it into a
database for analysis.

when I try to create an index on the query_time field of the json
structure I get:

ler=# select id,data->'message'->>'query_time' from dns_query limit 2;
  id |          ?column?
----+-----------------------------
   2 | 2019-05-13T01:35:59.822984Z
   3 | 2019-05-13T01:35:59.829801Z
(2 rows)

ler=# select id,(data->'message'->>'query_time')::timestamptz  from
dns_query limit 2;
  id |          timestamptz
----+-------------------------------
   2 | 2019-05-12 20:35:59.822984-05
   3 | 2019-05-12 20:35:59.829801-05
(2 rows)

ler=#

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

Is there any easy way to do this?  Or, what would the experts recommend
here?



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: [hidden email]
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106


Reply | Threaded
Open this post in threaded view
|

Re: create index on a jsonb timestamp field?

Larry Rosenman
On 05/18/2019 5:53 pm, Larry Rosenman wrote:

> I'm playing with DNSTAP (dnstap.info) data and loading it into a
> database for analysis.
>
> when I try to create an index on the query_time field of the json
> structure I get:
>
> ler=# select id,data->'message'->>'query_time' from dns_query limit 2;
>  id |          ?column?
> ----+-----------------------------
>   2 | 2019-05-13T01:35:59.822984Z
>   3 | 2019-05-13T01:35:59.829801Z
> (2 rows)
>
> ler=# select id,(data->'message'->>'query_time')::timestamptz  from
> dns_query limit 2;
>  id |          timestamptz
> ----+-------------------------------
>   2 | 2019-05-12 20:35:59.822984-05
>   3 | 2019-05-12 20:35:59.829801-05
> (2 rows)
>
> ler=#
>
> 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
>
> Is there any easy way to do this?  Or, what would the experts recommend
> here?

I found a work-around in making a column for query time, and populating
that in an UPDATE/INSERT trigger, and then making an index on that.


--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 214-642-9640                 E-Mail: [hidden email]
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106


Reply | Threaded
Open this post in threaded view
|

Re: create index on a jsonb timestamp field?

Tom Lane-2
In reply to this post by Larry Rosenman
Larry Rosenman <[hidden email]> 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.

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?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: create index on a jsonb timestamp field?

Larry Rosenman
On 05/18/2019 8:17 pm, Tom Lane wrote:

> Larry Rosenman <[hidden email]> 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: [hidden email]
US Mail: 5708 Sabbia Dr, Round Rock, TX 78665-2106


Reply | Threaded
Open this post in threaded view
|

Re: create index on a jsonb timestamp field?

Tom Lane-2
Larry Rosenman <[hidden email]> writes:

> On 05/18/2019 8:17 pm, Tom Lane wrote:
>> 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?

Well, I was just asking the question, not opining on whether it
was right or wrong.  GENERATED is a SQL-spec feature, and it might
be that the semantics the spec calls for wouldn't work without
the restriction.  I've not looked...

                        regards, tom lane