How Can We Monitor “No Incoming Messages” Data Exceptions?

Create a Demo Design

create table tbl (  
id int8,
-- ... Other fields, such as the completion status
state int, -- Completion status (1 indicates Completed)
deadts timestamp, -- Timeout
nts interval, -- Timeout interval, used to update the next notification time, for example, one notification per day
notify_times int default 0, -- Number of notifications
deadts_next timestamp -- Next notification time
);
create index idx_tbl_1 on tbl (deadts) where notify_times=0 and state<>1;  

create index idx_tbl_2 on tbl (deadts_next) where deadts_next is not null and state<>1;
with tmp1 as (  
update tbl set
deadts_next=now()+nts,
notify_times=notify_times+1
where ctid = any (array(
select ctid from tbl where
( deadts < now() and notify_times=0 and state<>1)
union all
select ctid from tbl where
( deadts_next < now() and deadts_next is not null and state<>1)
limit 10000 -- Obtains 10,000 timeout data records at a time.
))
returning *
)
select * from tmp1;
CTE Scan on tmp1  (cost=18163.25..18163.45 rows=10 width=48)
CTE tmp1
-> Update on tbl tbl_2 (cost=18151.05..18163.25 rows=10 width=54)
InitPlan 1 (returns $0)
-> Limit (cost=0.13..18151.03 rows=10000 width=6)
-> Append (cost=0.13..764699.60 rows=421301 width=6)
-> Index Scan using idx_tbl_1 on tbl (cost=0.13..169527.13 rows=369766 width=6)
Index Cond: (deadts < now())
-> Index Scan using idx_tbl_2 on tbl tbl_1 (cost=0.43..590959.46 rows=51535 width=6)
Index Cond: (deadts_next < now())
-> Tid Scan on tbl tbl_2 (cost=0.01..12.21 rows=10 width=54)
TID Cond: (ctid = ANY ($0))
(12 rows)

Create Performance Indicators

-- Processing of 0.1 billion data records is completed.  
insert into tbl select id, 1, now(), '5 min', 0, null from generate_series(1,100000000) t(id);

-- Processing of one million data records times out.
insert into tbl select id, 0, now(), '5 min', 0, null from generate_series(1,1000000) t(id);
with tmp1 as (  
update tbl set
deadts_next=now()+nts,
notify_times=notify_times+1
where ctid = any (array(
select ctid from tbl where
( deadts < now() and notify_times=0 and state<>1)
union all
select ctid from tbl where
( deadts_next < now() and deadts_next is not null and state<>1)
limit 10000 -- Obtains 10,000 timeout data records at a time.
))
returning *
)
select * from tmp1;


-- Planning

CTE Scan on tmp1 (cost=18163.25..18163.45 rows=10 width=48) (actual time=39.092..78.707 rows=10000 loops=1)
Output: tmp1.id, tmp1.state, tmp1.deadts, tmp1.nts, tmp1.notify_times, tmp1.deadts_next
Buffers: shared hit=75094 read=49 dirtied=49
CTE tmp1
-> Update on public.tbl tbl_2 (cost=18151.05..18163.25 rows=10 width=54) (actual time=39.089..74.637 rows=10000 loops=1)
Output: tbl_2.id, tbl_2.state, tbl_2.deadts, tbl_2.nts, tbl_2.notify_times, tbl_2.deadts_next
Buffers: shared hit=75094 read=49 dirtied=49
InitPlan 1 (returns $0)
-> Limit (cost=0.13..18151.03 rows=10000 width=6) (actual time=31.265..36.899 rows=10000 loops=1)
Output: tbl.ctid
Buffers: shared hit=11395
-> Append (cost=0.13..764699.60 rows=421301 width=6) (actual time=31.264..35.354 rows=10000 loops=1)
Buffers: shared hit=11395
-> Index Scan using idx_tbl_1 on public.tbl (cost=0.13..169527.13 rows=369766 width=6) (actual time=0.014..0.014 rows=0 loops=1)
Output: tbl.ctid
Index Cond: (tbl.deadts < now())
Buffers: shared hit=1
-> Index Scan using idx_tbl_2 on public.tbl tbl_1 (cost=0.43..590959.46 rows=51535 width=6) (actual time=31.249..33.870 rows=10000 loops=1)
Output: tbl_1.ctid
Index Cond: (tbl_1.deadts_next < now())
Buffers: shared hit=11394
-> Tid Scan on public.tbl tbl_2 (cost=0.01..12.21 rows=10 width=54) (actual time=39.017..43.529 rows=10000 loops=1)
Output: tbl_2.id, tbl_2.state, tbl_2.deadts, tbl_2.nts, (tbl_2.notify_times + 1), (now() + tbl_2.nts), tbl_2.ctid
TID Cond: (tbl_2.ctid = ANY ($0))
Buffers: shared hit=21395
Planning time: 0.301 ms
Execution time: 79.905 ms
Time: 79.905 ms

Conclusion

Original Source

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Journey to Editor Window Glory (part 1)

Image Compression Using Gulp and Imagemin

Writing Automated UI tests in .NET using Playwright and SpecFlow

Working with Stripe Webhooks & Firebase Cloud Functions

Architecture Reviews Without The Hassle

[Leetcode 700] Search in a Binary Search Tree

How To Install MySQL Server Application on Windows 10/11 | Easy Installation Guide

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Apache Airflow: Write your first DAG in Apache Airflow

Up and Running with Kafka (installation) in Simplest way

Form Fits Function: A Case Study on Integrating MongoDB and Redis with Apache Spark

from the above table we can easily understand that 1st offset will process the 24 rows(4+12+08)…