All we need is an easy explanation of the problem, so here it is.
This does NOT compile:
SELECT add_compression_policy(
hypertable => 'exchange.candles',
compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker))),
if_not_exists => TRUE);
The issue seems to be the SELECT
following INTERVAL
.
But this compiles:
WITH delay AS (SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker) myinterval)
SELECT add_compression_policy(
hypertable => 'exchange.candles',
compress_after => myinterval,
if_not_exists => TRUE) FROM delay;
My colleague and I can’t understand why.
Can anyone explain?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
Explanation
The function interval()
does not do what you seem to think. You can use the type name like this to cast a value (typed or untyped):
test=> SELECT interval '5 days 20:32:32.06';
interval
--------------------
5 days 20:32:32.06
But you cannot use it as function (followed by parentheses). That’s only implemented for some basic types. See:
There are two functions named "interval" registered in Postgres 14, one taking time
, the other interval
and integer
. Not what you want:
test=> SELECT (proargtypes::regtype[])[:] FROM pg_proc WHERE proname = 'interval';
proargtypes
----------------------------
{"time without time zone"}
{interval,integer}
You seem to want a cast, but you don’t need that to begin with, as the result of your convoluted expression is interval
already.
Solution
Untangled:
SELECT add_compression_policy(hypertable => 'exchange.candles'
, compress_after => (SELECT current_date - min(last_ts) FROM exchange.capture_tracker)
, if_not_exists => TRUE);
now()::DATE::timestamp
burns down to CURRENT_DATE
. The latter is date
instead of timestamp
, but the result of the expression is the same. (Either depends on the timezone
setting of your current session.)
Or, even simpler:
SELECT add_compression_policy(hypertable => 'exchange.candles'
, compress_after => current_date - min(last_ts)
, if_not_exists => TRUE)
FROM exchange.capture_tracker;
Since the only reference to the table is min(last_ts)
, this always returns a single row, even if the table is empty. And the result is the same as for the above.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0