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

user_10263515 0
Viewed 225

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
timescaledb jooq java
2 Answers

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.)

user_84651660

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();
user_102635150
Related