Why does time_bucket_gapfill consistently throw a PSQLException, at 10th request?

Question

I have a Spring Boot application, with a REST service where I use JdbcTemplate. Then I have a PostgreSQL with TimescaleDB (verson 2.3.0) where the data is stored.

In one of my endpoinds, I call the following code to get some timestamps from the database in the clients local time zone:

SELECT time_bucket_gapfill(CAST(:numOfHours * INTERVAL '1 hour' AS INTERVAL), 
       timestamp AT TIME ZONE 'UTC' AT TIME ZONE :timezone,
       :start AT TIME ZONE :timezone, :end AT TIME ZONE :timezone) AS time
FROM info
WHERE timestamp >= :start AND timestamp < :end
GROUP BY time

When I call that specific endpoint it returns the data perfectly the first 9 times, and then on the 10th time, it throws the following SQL error:

ERROR: invalid time_bucket_gapfill argument: start must be a simple expression

Answer 1

The TimescaleDB manual states:

Note that explicitly provided start and stop or derived from WHERE clause values need to be simple expressions. Such expressions should be evaluated to constants at the query planning. For example, simple expressions can contain constants or call to now(), but cannot reference to columns of a table.

What they are trying to say is that these arguments must be constants. So you cannot use a parameter here.

Why this works for the first 10 executions is because of the way the JDBC driver and PostgreSQL handle these parameters:

• for the first 5 executions of the JDBC java.sql.PreparedStatement, the PostgreSQL driver interpolates the parameters into the query and sends a simple query string

• from the sixth execution on, the JDBC driver deems it worth creating a named prepared statement in PostgreSQL

• during the first five executions of that prepared statement, PostgreSQL generates a custom plan that uses the actual parameter values

• only from the sixth execution on, PostgreSQL will consider a generic plan, where the parameters are placeholders

Executing this generic plan causes the TimescaleDB error.

So there are several remedies:

1. Don't use parameters there. That is the best and most reliable solution. However, then you cannot use a prepared statement, but you have to construct the query string each time (dynamic SQL).

2. See the JDBC driver documentation:

   The driver uses server side prepared statements by default when PreparedStatement API is used. In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold connection property). An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements.

   That allows you to work around the problem by setting prepareThreshold to 0, at the price of worse performance.

3. Set the PostgreSQL parameter plan_cache_mode to force_custom_plan to avoid the use of generic plans. This could affect your overall performance negatively.

All three solutions reduce the effectiveness of prepared statements, but that's the price you have to pay to work around this limitation of TimescaleDB.