-
Notifications
You must be signed in to change notification settings - Fork 10
/
sequence_overflow.sql
51 lines (48 loc) · 1.83 KB
/
sequence_overflow.sql
1
2
3
4
5
6
7
8
9
10
11
12
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
/*
* Copyright (c) 2019-2024. Ivan Vakhrushev and others.
* https://github.com/mfvanek/pg-index-health-sql
*
* Licensed under the Apache License 2.0
*/
-- Finds sequences that may overflow in the near future
--
-- Based on query from https://habr.com/ru/articles/800121/
with
all_sequences as (
select
s.seqrelid::regclass::text as sequence_name,
s.seqtypid::regtype as data_type,
s.seqstart as start_value,
s.seqmin as min_value,
s.seqmax as max_value,
s.seqincrement as increment_by,
case
when has_sequence_privilege(c.oid, 'select,usage'::text) then pg_sequence_last_value(c.oid::regclass)
else null::bigint
end as last_value
from
pg_sequence s
inner join pg_class c on c.oid = s.seqrelid
left join pg_namespace nsp on nsp.oid = c.relnamespace
where
not pg_is_other_temp_schema(nsp.oid) and -- not temporary
c.relkind = 'S'::char and -- sequence object
not s.seqcycle and -- skip cycle sequences
nsp.nspname = :schema_name_param::text
),
sequence_state as (
select
t.sequence_name,
t.data_type,
case
-- ascending or descending sequence
when t.increment_by > 0 then 100.0 * (t.max_value - coalesce(t.last_value, t.start_value)) / (t.max_value - t.min_value)
else 100.0 * (coalesce(t.last_value, t.start_value) - t.min_value) / (t.max_value - t.min_value)
end ::numeric(5, 2) as remaining_percentage -- percentage of remaining values
from all_sequences t
)
select s.*
from sequence_state s
where
s.remaining_percentage <= :remaining_percentage_threshold::numeric(5, 2)
order by s.sequence_name;