13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
owner bigint not null);
create unique index rsstg_source__source_id on rsstg_source(source_id);
create unique index rsstg_source__channel_id__owner on rsstg_source(channel_id, owner);
create index rsstg_source__owner on rsstg_source(owner);
create table rsstg_post (
source_id integer not null,
date int not null,
url text not null,
hour smallint not null generated always as (extract('hour' from posted)) stored,
FOREIGN KEY (source_id) REFERENCES rsstg_source(source_id) on delete cascade,
);
create unique index rsstg_post__url on rsstg_post(url);
create index rsstg_post__hour on rsstg_post(hour);
create index rsstg_post__posted_hour on rsstg_post(posted,hour);
create or replace view rsstg_order as
select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch, owner
from rsstg_source natural left join
(select source_id, count(*) as activity
from rsstg_post where
hour = extract('hour' from now())::smallint
and posted > now() - interval '7 days'
group by source_id) as act
where enabled
order by next_fetch;
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
|
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
owner bigint not null);
create unique index rsstg_source__source_id on rsstg_source(source_id);
create unique index rsstg_source__channel_id__owner on rsstg_source(channel_id, owner);
create index rsstg_source__owner on rsstg_source(owner);
create table rsstg_post (
source_id integer not null,
posted timestamptz not null,
url text not null,
hour smallint not null generated always as (extract('hour' from posted at time zone 'utc')) stored,
hxm smallint not null generated always as (hxm(posted)) stored,
FOREIGN KEY (source_id) REFERENCES rsstg_source(source_id) on delete cascade,
);
create unique index rsstg_post__url on rsstg_post(url);
create index rsstg_post__hour on rsstg_post(hour);
create index rsstg_post__posted_hour on rsstg_post(posted,hour);
create index rsstg_post__hxm on rsstg_post(hxm);
create index rsstg_post__posted_hxm on rsstg_post(posted,hxm);
create or replace view rsstg_order_old as
select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch, owner
from rsstg_source natural left join
(select source_id, count(*) as activity
from rsstg_post where
hour = extract('hour' from now())::smallint
and posted > now() - interval '7 days'
and posted < now() - interval '1 hour'
group by source_id) as act
where enabled
order by next_fetch;
create or replace function hxm(timestamptz) returns smallint
as $$ select(extract('hour' from $1) * extract('minute' from $1)); $$
language sql immutable returns null on null input;
create or replace view rsstg_order as
select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch, owner
from rsstg_source natural left join
(select source_id, count(*) as activity
from rsstg_post where
(
(hxm > hxm(now()) - 30 and hxm < hxm(now()) + 30)
or (hxm < 30 and hxm < hxm(now()) + 30 - 1440)
or (hxm > 1410 and hxm > 1440 + hxm(now()) - 30)
)
and posted < now() - interval '1 hour'
and posted > now() - interval '7 days'
group by source_id) as act
where enabled
order by next_fetch;
|