Using Timescale DB's Gapfill with JOOQ throws argument: start cannot be NULL

Question

I have a Timescale DB running and am using JOOQ to access the data.

I am using gapfill() for

dslContext
        .select(Routines.timeBucketGapfill5(
                Routines.createInterval(DURATION), table.TIME, null, null).as(FieldName.TIME_BUCKET),
                ifnull(count(table.STATUS), 0).as(FieldName.STATUS))
        .from(table)
        .where(table.ID.in(ids)
                .and(table.TIME.ge(Timestamp.from(Instant.now().minus(Duration.ofSeconds(DURATION)))))
                .and(table.TIME.le(Timestamp.from(Instant.now())))
                .and(table.STATUS.eq(status)))
        .groupBy(field(FieldName.TIME_BUCKET))
        .fetch()

Sometimes I get a

org.postgresql.util.PSQLException: ERROR: invalid time_bucket_gapfill argument: start cannot be NULL
  Hinweis: You can either pass start and finish as arguments or in the WHERE clause

How can start be NULL if I always have a where clause with a greaterEquals and lessEquals?

I was able to log the SQL statement. If I run that query directly on the DB it works fine.

select "public"."time_bucket_gapfill"("bucket_width" := cast("public"."create_interval"("seconds" := 43200) as "pg_catalog"."interval"), "ts" := cast("public"."device_health"."time" as timestamp), "start" := cast(null as timestamp), "finish" := cast(null as timestamp)) as "time_bucket", coalesce(count("public"."device_health"."health"), 0) as "health" from "public"."device_health" where ("public"."device_health"."device" in ('700004', '700009', '700008', '700005', '700007', '700000', '700003', '700001', '700002', '700006') and "public"."device_health"."time" >= timestamp '2020-03-11 13:59:20.0564238' and "public"."device_health"."time" <= timestamp '2020-03-25 13:59:20.0564238' and "public"."device_health"."health" = 'OK') group by time_bucket

score 2 · Answer 1 · answered Mar 25 '20 at 11:42

2

Are you seeing the same error if you pass in those same args as the start and finish optional args in the gapfill call? Are you able to capture the actual SQL that JOOQ is synthesizing?

(Aside, for more back-and-forth help, you might find slack.timescale.com helpful.)

answered Mar 25 '20 at 11:42

Mike Freedman

1,692
9
9

I have a Problem filling the start and finish args becuase JOOQ generated the Routine timeBucketGapfill5(Field – Sören Linnemann Mar 25 '20 at 12:09
1

@SörenLinnemann: You can omit optional arguments in jOOQ by creating a `new TimeBucketGapfill5()` instance, and not setting those values to the instance. The auxiliary function `Routines.timeBucketGapfill5()` will always set *all* arguments. – Lukas Eder Mar 26 '20 at 08:47

score 2 · Accepted Answer · edited Mar 31 '20 at 08:11

To fix the issue I now pass start and finish as JOOQ DSL.val() in form of a Field<Timestamp> and deleted the start/finish part from where clause.

I have no clue what exactly cause the error but this fixes it.

Here is the working code:


var now = Instant.now();
Timestamp startTimestamp = Timestamp.from(now.minus(Duration.ofSeconds(DURATION)));
Timestamp finishTimestamp = Timestamp.from(now);

dslContext
        .select(Routines.timeBucketGapfill5(
                        Routines.createInterval(DURATION),
                        table.TIME,
                        DSL.val(startTimestamp),
                        DSL.val(finishTimestamp))
                                .as(FieldName.TIME_BUCKET),
                        table.HEALTH,
                        ifnull(count(table.STATUS), 0).as(FieldName.STATUS))
                .from(table)
                .where(table.ID.in(ids))
                .groupBy(field(FieldName.TIME_BUCKET))
                .fetch();

Using Timescale DB's Gapfill with JOOQ throws argument: start cannot be NULL

2 Answers2