Idea: Add first() and last() aggregate functions to the main release

2023-03-11 Thread Ben Clements 
Idea/request for enhancement:

Add the first() and last() aggregate functions to the main release (CREATE
EXTENSION first_last_agg).

Use Case: PostgreSQL equivalent to Oracle's MAX(...) KEEP (DENSE_RANK
FIRST/LAST ORDER BY ...) 
(https://dba.stackexchange.com/a/324646/100880)

GitHub: https://github.com/wulczer/first_last_agg

Reason: As a non-dba, I can’t install additional modules like first_last_agg
.

Thanks,
-Ben

ERROR: only immutable functions supported in continuous aggregate view

2023-03-11 Thread Martijn de Munnik 
Hi List,


I'm trying to create a TimescaleDB continuous aggregate using the
following statement:

CREATE MATERIALIZED VIEW "navigation_data_5min_hidden" WITH
(timescaledb.continuous) AS
SELECT
   public.time_bucket(INTERVAL '5 min', "time") AS "time",
   "context",
   jsonb_build_object(
     'max', max("value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue'),
     'min', min("value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue'),
     'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (WHERE "path"
= 'navigation.courseOverGroundTrue' AND "value" IS NOT NULL),
     'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "path" =
'navigation.courseOverGroundTrue' AND "value" IS NOT NULL),
     'average', public.average(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue')),
     'integral', public.average(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue')) * EXTRACT(EPOCH FROM INTERVAL '5 min'),
     'stddev', public.stddev(public.stats_agg("value"::double precision)
FILTER (WHERE "path" = 'navigation.courseOverGroundTrue')),
     'skewness', public.skewness(public.stats_agg("value"::double
precision) FILTER (WHERE "path" = 'navigation.courseOverGroundTrue')),
     'first_time', public.first_time(public.time_weight('Linear',
"time", "value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue')),
     'first_val', public.first_val(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue')),
     'last_time', public.last_time(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue')),
     'last_val', public.last_val(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.courseOverGroundTrue'))
   ) AS "navigation.courseOverGroundTrue",
   jsonb_build_object(
     'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (WHERE "path"
= 'navigation.datetime' AND "value" IS NOT NULL),
     'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "path" =
'navigation.datetime' AND "value" IS NOT NULL),
     'first_time', public.first("time", "time") FILTER (WHERE "path" =
'navigation.datetime' AND "time" IS NOT NULL),
     'first_val', public.first("value"#>>'{}', "time") FILTER (WHERE
"path" = 'navigation.datetime' AND "time" IS NOT NULL),
     'last_time', public.last("time", "time") FILTER (WHERE "path" =
'navigation.datetime' AND "time" IS NOT NULL),
     'last_val', public.last("value"#>>'{}', "time") FILTER (WHERE
"path" = 'navigation.datetime' AND "time" IS NOT NULL)
   ) AS "navigation.datetime",
   jsonb_build_object(
     'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (WHERE "path"
= 'navigation.gnss.methodQuality' AND "value" IS NOT NULL),
     'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "path" =
'navigation.gnss.methodQuality' AND "value" IS NOT NULL),
     'first_time', public.first("time", "time") FILTER (WHERE "path" =
'navigation.gnss.methodQuality' AND "time" IS NOT NULL),
     'first_val', public.first("value"#>>'{}', "time") FILTER (WHERE
"path" = 'navigation.gnss.methodQuality' AND "time" IS NOT NULL),
     'last_time', public.last("time", "time") FILTER (WHERE "path" =
'navigation.gnss.methodQuality' AND "time" IS NOT NULL),
     'last_val', public.last("value"#>>'{}', "time") FILTER (WHERE
"path" = 'navigation.gnss.methodQuality' AND "time" IS NOT NULL)
   ) AS "navigation.gnss.methodQuality",
   jsonb_build_object(
     'max', max("value"::double precision) FILTER (WHERE "path" =
'navigation.gnss.satellites'),
     'min', min("value"::double precision) FILTER (WHERE "path" =
'navigation.gnss.satellites'),
     'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (WHERE "path"
= 'navigation.gnss.satellites' AND "value" IS NOT NULL),
     'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "path" =
'navigation.gnss.satellites' AND "value" IS NOT NULL),
     'average', public.average(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.gnss.satellites')),
     'integral', public.average(public.time_weight('Linear', "time",
"value"::double precision) FILTER (WHERE "path" =
'navigation.gnss.satellites')) * EXTRACT(EPOCH FROM INTERVAL '5 min'),
     'stddev', public.stddev(public.stats_agg("value"::double precision)
FILTER (WHERE "path" = 'navigation.gnss.satellites')),
     'skewness', public.skewness(public.stats_agg("value"::double
precision) FILTER (WHERE "path" = 'navigation.gnss.satellites')),
     'first_time', public.first_time(public.time_weight('Linear',
"time", "value"::double precision) FILTER (WHERE "path" =
'navigation.gnss.satellites')),
     'first_val', public.first_val(public.time_weight('Linear', "time",
"value"::double precision) FILTE

Re: ERROR: only immutable functions supported in continuous aggregate view

2023-03-11 Thread Tom Lane 
Martijn de Munnik  writes:
> The error is caused by the jsonb_build_object function, is there any
> reason why this function is not IMMUTABLE?

It can invoke arbitrary datatype output functions, some of which are
not immutable.  We unfortunately lack any infrastructure that would
allow reasoning that "all the types used in this specific invocation
have immutable output functions, so it'd be OK to consider this
invocation immutable".  So we have to assume the worst when labeling
jsonb_build_object.  (Even if we had such infrastructure, I doubt
it would save you in this use-case, because it looks like some of
the values you're converting are timestamps, which respond to the
DateStyle and TimeZone settings.)

You could, on these grounds, argue that jsonb_build_object and other
functions with this problem actually need to be VOLATILE.  But we've
established a project convention that I/O functions should be at
worst STABLE, allowing calling functions to be STABLE as well.

regards, tom lane

confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

2023-03-11 Thread Atul Kumar 
Hi,

Could someone help me in telling the difference between these
three parameters
1. max_standby_archive_delay
2. max_standby_streaming_delay
3. recovery_min_apply_delay

My basic motive is to make the standby database server to be delayed to
apply the changes on itself,  if any data has been accidentally
deleted/updated/ truncated from the primary server.

Which parameter do I need to configure to serve this purpose ? And
When will the remaining two parameters be used ?

It would be great if anyone can explain them with a brief example.



Regards.

Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay

2023-03-11 Thread Ron 

On 3/11/23 14:23, Atul Kumar wrote:

Hi,

Could someone help me in telling the difference between these 
three parameters

1. max_standby_archive_delay
2. max_standby_streaming_delay
3. recovery_min_apply_delay

My basic motive is to make the standby database server to be delayed to 
apply the changes on itself,  if any data has been accidentally 
deleted/updated/ truncated from the primary server.


What you're looking for is that WAL files be "aggregated and *optimized*" by 
removing statements which don't have any impact on the final state of instance.


That's great for *restoring* instances with update-heavy databases, and can 
only implemented by backup/restore software, or roll-your-own log shipping.  
Just as importantly, that aggregation-optimization software has it's own 
costs (time and disk space), since it takes time to scan through all the 
recovery logs, keeping track of what must stay, what can be tossed, and the 
final state.


--
Born in Arizona, moved to Babylonia.

Re: Idea: Add first() and last() aggregate functions to the main release

2023-03-11 Thread Erwin Brandstetter 
On Sat, 11 Mar 2023 at 23:38, Ben Clements 
wrote:

> Idea/request for enhancement:
>
> Add the first() and last() aggregate functions to the main release (CREATE
> EXTENSION first_last_agg).
>
> Use Case: PostgreSQL equivalent to Oracle's MAX(...) KEEP (DENSE_RANK
> FIRST/LAST ORDER BY ...) 
> (https://dba.stackexchange.com/a/324646/100880)
>
> GitHub: https://github.com/wulczer/first_last_agg
>
> Reason: As a non-dba, I can’t install additional modules like
> first_last_agg.
>


For what it's worth, I support the inclusion of first() and last() in the
main release. It makes queries involving multiple aggregate functions a lot
simpler. Most hosted services only allow official contrib modules.
The module has been around for more than 10 years now. First on
https://www.pgxn.org/dist/first_last_agg, then on
https://github.com/wulczer/first_last_agg. And it's been available from
apt.postgresql.org for a long time now.
Related questions pop up on Stackoverflow every now and then.
(I don't see anything related in the Postgres Todo, yet.)

The implementation in the additional module first_last_agg looks very
simple and straight-forward. Might be an easy task to incorporate into
mainline Postgres. But my C foo is just not good enough to judge whether
the implementation is ideal.

Regards
Erwin