Add check for sequences overflow (how much is left before the limit value)

[mfvanek]

See article https://habr.com/ru/articles/800121/

SELECT
    schemaname,    -- наименование схемы
    sequencename,  -- наименование последовательности
    CASE           -- проверка направления роста последовательности
        WHEN increment_by > 0 THEN 100.0*(max_value - COALESCE(last_value, start_value))/(max_value - min_value)
        ELSE 100.0*(COALESCE(last_value, start_value) - min_value)/(max_value - min_value)
    END::numeric(5, 2) -- значение в %
FROM pg_catalog.pg_sequences
WHERE NOT cycle -- исключаем цикличные последовательности

[BLoHny]

May i take this issue ?

[mfvanek]

Updated sql query

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
        join pg_class c on c.oid = s.seqrelid
        left join pg_namespace n on n.oid = c.relnamespace
    where
        not pg_is_other_temp_schema(n.oid) -- not temporary
        and c.relkind = 'S'::char -- sequence object
        and not s.seqcycle -- skip cycle sequences
        and n.nspname = :schema_name_param::text
),
sequence_state as (
    select
        t.sequence_name,
        t.data_type,
        case when t.increment_by > 0 -- ascending or descending sequence
        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 as t
)
select *
from sequence_state as s
where
    s.remaining_percentage <= :remaining_percentage_threshold::numeric(5, 2)
order by s.sequence_name;

[mfvanek]

Hi @BLoHny I've merged PR with a new sql query

[BLoHny]

@Immutable
public class SequenceState implements DbObject, TableNameAware {

    private final String sequenceName;
    private final String dataType;
    private final double remainingPercentage;

Should we create a class like this?

[mfvanek]

Should we create a class like this?

@BLoHny Yes, we should.
You don't need TableNameAware here. Only DbObject

[BLoHny]

    @Override
    public void execute(@Nonnull Statement statement) throws SQLException {
        statement.execute(String.format(
            "drop sequence if exists %1$s.seq_1; " +
                "create sequence %1$s.seq_1 as smallint increment by 1 maxvalue 100 start with 92;", schemaName));

        statement.execute(String.format(
            "drop sequence if exists %1$s.seq_2; " +
                "create sequence %1$s.seq_2 as smallint increment by 1 maxvalue 100 start with 99 cycle;", schemaName));
    }

Should we create and test the following statement?

[mfvanek]

Should we create and test the following statement?

Yeah, looks good.

You also need to add a sequence with different data type (integer for example, not only smallint) and different remaining_percentage. It will allow to test t.data_type and s.remaining_percentage <= :remaining_percentage_threshold::numeric(5, 2) in sql query.

[BLoHny]

    @ParameterizedTest
    @ValueSource(strings = {PgContext.DEFAULT_SCHEMA_NAME, "custom"})
    void onDatabaseWithSequences(final String schemaName) {     
        executeTestOnDatabase(schemaName, DatabasePopulator::withSequenceOverflow, ctx -> {
            assertThat(check)
                .executing(ctx)
                .hasSize(5)
                .containsExactly(
                    SequenceState.of("seq_1", "smallint", 8.08),
                    SequenceState.of("seq_2", "smallint", 1.00),
                    SequenceState.of("seq_3", "integer", 98.00),
                    SequenceState.of("seq_5", "bigint", 50.00));
        });
    }

I ran this test code but error occur No value specified for parameter 2. where s.remaining_percentage <= ?::numeric(5, 2) seems to be a problem with this

    @Nonnull
    @Override
    public List<SequenceState> check(@Nonnull PgContext pgContext) {
        return executeQuery(pgContext, rs -> {
            final String sequenceName = rs.getString("sequence_name");
            final String dataType = rs.getString("data_type");
            final double remainingPercentage = rs.getDouble("remaining_percentage");
            return SequenceState.of(sequenceName, dataType, remainingPercentage);
        });
    }

Should we need a new executeQuery extension method?

[mfvanek]

Should we need a new executeQuery extension method?

@BLoHny You need to add a new field to PgContext https://github.com/mfvanek/pg-index-health/blob/439a77da39a11f4b776ed238721343500d1ef67b/pg-index-health-model/src/main/java/io/github/mfvanek/pg/model/PgContext.java#L32

Then you need to add a new method to QueryExecutors https://github.com/mfvanek/pg-index-health/blob/439a77da39a11f4b776ed238721343500d1ef67b/pg-index-health/src/main/java/io/github/mfvanek/pg/utils/QueryExecutors.java#L77

[mfvanek]

• [x] Add example to demo apps

[mfvanek]

@BLoHny Thank you for your participation! All your changes are in the master branch and are reflected in the statistics.