Help with writing a generate_series(tsmultirange, interval)
Hello all!
I’m excited for multi ranges, as they fit nicely into a scheduling app. What
I’m trying to express is something along the lines of « Every weekday from
2021-08-01 and 2021-10-01, from 9 AM to 8 PM, every 90 minutes ». You can think
of public transit for the model.
Initially, I was going to create a table with every departure recorded, and was
going to refresh the table on every write to the parent table, but that means
maintaining many rows for every change to the schedule. Then, I remembered
multi ranges in PG14, and they fit nicely with what I had in mind.
Now that I can store the info I want, I’d like to iterate over the ranges, so I
can generate the exact departure times, something similar to this:
SELECT instant
FROM generate_series(
'{["2021-08-02 08:00:00","2021-08-02 11:00:00"),["2021-08-03
08:00:00","2021-08-03 20:00:00"]’}
, interval ’90 minutes’) as instant;
2021-08-02 08:00
2021-08-02 09:30
— 2021-08-02 11:00 excluded as the range excludes its upper bound
2021-08-03 08:00:00
2021-08-03 09:30:00
2021-08-03 11:00:00
2021-08-03 12:30:00
2021-08-03 14:00:00
2021-08-03 15:30:00
2021-08-03 17:00:00
2021-08-03 18:30:00
2021-08-03 20:00:00 — included, as the upper bound is inclusive
That function doesn’t exist, and I can’t seem to find a function to iterate
over a multi range either. Does such a function exist? I’m specifically looking
at https://www.postgresql.org/docs/14/functions-range.html.
This is a toy application, a spike to see what’s possible.
I wanted to avoid maintaining a table with hundreds of rows per route, if a
route runs frequently enough (every 10 minutes, 8 AM to 8 PM, over a year). Of
course, I can avoid refreshing the departures table if the schedule hasn’t
changed, but still, preparing this table will not take a constant amount of
time; e.g. it will depend on the schedule’s size.
Any tips appreciated!
François
Re: Help with writing a generate_series(tsmultirange, interval)
On 7/31/21 11:59 AM, François Beausoleil wrote:
Hello all!
I’m excited for multi ranges, as they fit nicely into a scheduling app. What
I’m trying to express is something along the lines of « Every weekday from
2021-08-01 and 2021-10-01, from 9 AM to 8 PM, every 90 minutes ». You can think
of public transit for the model.
Initially, I was going to create a table with every departure recorded, and was
going to refresh the table on every write to the parent table, but that means
maintaining many rows for every change to the schedule. Then, I remembered
multi ranges in PG14, and they fit nicely with what I had in mind.
Now that I can store the info I want, I’d like to iterate over the ranges, so I
can generate the exact departure times, something similar to this:
SELECT instant
FROM generate_series(
'{["2021-08-02 08:00:00","2021-08-02 11:00:00"),["2021-08-03
08:00:00","2021-08-03 20:00:00"]’}
, interval ’90 minutes’) as instant;
2021-08-02 08:00
2021-08-02 09:30
— 2021-08-02 11:00 excluded as the range excludes its upper bound
2021-08-03 08:00:00
2021-08-03 09:30:00
2021-08-03 11:00:00
2021-08-03 12:30:00
2021-08-03 14:00:00
2021-08-03 15:30:00
2021-08-03 17:00:00
2021-08-03 18:30:00
2021-08-03 20:00:00 — included, as the upper bound is inclusive
That function doesn’t exist, and I can’t seem to find a function to iterate
over a multi range either. Does such a function exist? I’m specifically looking
at https://www.postgresql.org/docs/14/functions-range.html.
This is a toy application, a spike to see what’s possible.
I wanted to avoid maintaining a table with hundreds of rows per route, if a
route runs frequently enough (every 10 minutes, 8 AM to 8 PM, over a year). Of
course, I can avoid refreshing the departures table if the schedule hasn’t
changed, but still, preparing this table will not take a constant amount of
time; e.g. it will depend on the schedule’s size.
Any tips appreciated!
How about:
SELECT
*
FROM
generate_series ('2021-08-02 08:00:00'::timestamp, '2021-08-02
10:59:00'::timestamp, interval '90 minutes') AS instant
UNION
SELECT
*
FROM
generate_series ('2021-08-03 08:00:00'::timestamp, '2021-08-03
20:00:00'::timestamp, interval '90 minutes') AS instant
ORDER BY instant;
instant
-
2021-08-02 08:00:00
2021-08-02 09:30:00
2021-08-03 08:00:00
2021-08-03 09:30:00
2021-08-03 11:00:00
2021-08-03 12:30:00
2021-08-03 14:00:00
2021-08-03 15:30:00
2021-08-03 17:00:00
2021-08-03 18:30:00
2021-08-03 20:00:00
François
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Help with writing a generate_series(tsmultirange, interval)
Hello Adrian,
> Le 31 juill. 2021 à 15:49, Adrian Klaver a écrit :
>
> On 7/31/21 11:59 AM, François Beausoleil wrote:
>> Hello all!
>> I’m excited for multi ranges, as they fit nicely into a scheduling app. What
>> I’m trying to express is something along the lines of « Every weekday from
>> 2021-08-01 and 2021-10-01, from 9 AM to 8 PM, every 90 minutes ». You can
>> think of public transit for the model.
>> Initially, I was going to create a table with every departure recorded, and
>> was going to refresh the table on every write to the parent table, but that
>> means maintaining many rows for every change to the schedule. Then, I
>> remembered multi ranges in PG14, and they fit nicely with what I had in mind.
>> Now that I can store the info I want, I’d like to iterate over the ranges,
>> so I can generate the exact departure times, something similar to this:
>> SELECT instant
>> FROM generate_series(
>> '{["2021-08-02 08:00:00","2021-08-02 11:00:00"),["2021-08-03
>> 08:00:00","2021-08-03 20:00:00"]’}
>> , interval ’90 minutes’) as instant;
>> 2021-08-02 08:00
>> 2021-08-02 09:30
>> — 2021-08-02 11:00 excluded as the range excludes its upper bound
>> 2021-08-03 08:00:00
>> 2021-08-03 09:30:00
>> 2021-08-03 11:00:00
>> 2021-08-03 12:30:00
>> 2021-08-03 14:00:00
>> 2021-08-03 15:30:00
>> 2021-08-03 17:00:00
>> 2021-08-03 18:30:00
>> 2021-08-03 20:00:00 — included, as the upper bound is inclusive
>> That function doesn’t exist, and I can’t seem to find a function to iterate
>> over a multi range either. Does such a function exist? I’m specifically
>> looking at https://www.postgresql.org/docs/14/functions-range.html.
>> This is a toy application, a spike to see what’s possible.
>> I wanted to avoid maintaining a table with hundreds of rows per route, if a
>> route runs frequently enough (every 10 minutes, 8 AM to 8 PM, over a year).
>> Of course, I can avoid refreshing the departures table if the schedule
>> hasn’t changed, but still, preparing this table will not take a constant
>> amount of time; e.g. it will depend on the schedule’s size.
>> Any tips appreciated!
>
> How about:
>
> SELECT
>*
> FROM
>generate_series ('2021-08-02 08:00:00'::timestamp, '2021-08-02
> 10:59:00'::timestamp, interval '90 minutes') AS instant
> UNION
> SELECT
>*
> FROM
>generate_series ('2021-08-03 08:00:00'::timestamp, '2021-08-03
> 20:00:00'::timestamp, interval '90 minutes') AS instant
> ORDER BY instant;
Yes, in fact, I wrote the following:
--
CREATE FUNCTION generate_series(tstzrange, interval) RETURNS SETOF timestamp
with time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
CREATE FUNCTION generate_series(tsrange, interval) RETURNS SETOF timestamp
without time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
That was the easy part. My end goal is to iterate over a tsmultirange: I would
like to get each individual range from a given multi range. Ideally, I’d like
to do that without parsing the textual version of the multi range.
While mowing the lawn, I thought that since the syntax of multi ranges is
similar to arrays, maybe I could use unnest(), but sadly, that was not to be
the case:
# select
unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
ERROR: function unnest(tsmultirange) does not exist
LINE 1: select unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-...
Apparently, PG can accept multi range values, but can’t do much with them at
the time, except to check for inclusion/exclusion.
Thanks for your time!
François
>> François
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
Re: Help with writing a generate_series(tsmultirange, interval)
On 7/31/21 5:16 PM, François Beausoleil wrote:
Hello Adrian,
Yes, in fact, I wrote the following:
--
CREATE FUNCTION generate_series(tstzrange, interval) RETURNS SETOF
timestamp with time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
CREATE FUNCTION generate_series(tsrange, interval) RETURNS SETOF
timestamp without time zone AS $$
SELECT n
FROM generate_series(lower($1), upper($1), $2) AS t0(n)
WHERE $1 @> n
$$ LANGUAGE sql immutable;
That was the easy part. My end goal is to iterate over a tsmultirange: I
would like to get each individual range from a given multi range.
Ideally, I’d like to do that without parsing the textual version of the
multi range.
While mowing the lawn, I thought that since the syntax of multi ranges
is similar to arrays, maybe I could use unnest(), but sadly, that was
not to be the case:
# select
unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
ERROR: function unnest(tsmultirange) does not exist
LINE 1: select unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-...
Apparently, PG can accept multi range values, but can’t do much with
them at the time, except to check for inclusion/exclusion.
I see your mowing the lawn and raise walking the dog. This rang some
bells and then I remembered when in doubt consult depesz:
https://www.depesz.com/2021/07/15/how-to-get-list-of-elements-from-multiranges/
https://www.postgresql.org/message-id/20210715121508.ga30...@depesz.com
Thanks for your time!
François
François
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Help with writing a generate_series(tsmultirange, interval)
=?utf-8?Q?Fran=C3=A7ois_Beausoleil?= writes:
> While mowing the lawn, I thought that since the syntax of multi ranges is
> similar to arrays, maybe I could use unnest(), but sadly, that was not to be
> the case:
> # select
> unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
> ERROR: function unnest(tsmultirange) does not exist
That's fixed for beta3:
regression=# select
unnest('{[2021-08-02,2021-08-04],[2021-08-07,2021-08-09)}'::tsmultirange);
unnest
---
["2021-08-02 00:00:00","2021-08-04 00:00:00"]
["2021-08-07 00:00:00","2021-08-09 00:00:00")
(2 rows)
regards, tom lane
postgres vacuum memory limits
Hello, when maintenance_work_mem and autovacuum_work_mem are set, my understanding is that the vacuum and autovacuum sessions should be limited to use the memory limits set by these parameters. But I am seeing more memory being used than these limits by autovacuum sessions, any reason why this would happen? Please see below examples, where maintenance_work_mem is set to 20mb and shared_buffers is 128mb. When I see the memory for this session in top, it shows 162mb. But when default_statistics_target is increased to 3000, the session usage is 463mb, which is way more than 20mb maintenance_work_mem and 128mb shared_buffer. Shouldn't the process memory be capped to 20+128mb? postgres=# show maintenance_work_mem ; maintenance_work_mem -- 20MB (1 row) postgres=# vacuum analyze mdm_context; VACUUM postgres=# show shared_buffers; shared_buffers 128MB (1 row) PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 62246 postgres 20 0 422892 165996 139068 R 57.1 15.7 25:06.34 postgres: postgres postgres [local] VACUUM postgres=# show default_statistics_target; default_statistics_target --- 100 (1 row) postgres=# set default_statistics_target=3000;SET postgres=# vacuum analyze mdm_context; VACUUM PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 62246 postgres 20 0 876132 474384 2976 R 62.9 47.6 25:11.41 postgres: postgres postgres [local] VACUUM
Re: postgres vacuum memory limits
On Saturday, July 31, 2021, Ayub M wrote: > But when default_statistics_target is increased to 3000, the session usage > is 463mb > IIUC, the analyze process doesn’t consult maintenance_work_mem. It simply creates an array, in memory, to hold the random sample of rows needed for computing the requested statistics. I skimmed the docs but didn’t get a firm answer beyond the fact that vacuum is an example command that consult maintenance_work_mem and analyze is not mentioned in the same list. I did find: ‘The largest statistics target among the columns being analyzed determines the number of table rows sampled to prepare the statistics. Increasing the target causes a proportional increase in the time and space needed to do ANALYZE.” David J.
