New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add check for sequences overflow (how much is left before the limit value) #363
Comments
May i take this issue ? |
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; |
Hi @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? |
@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? |
Yeah, looks good. You also need to add a sequence with different data type ( |
@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. @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? |
@BLoHny pg-index-health/pg-index-health-model/src/main/java/io/github/mfvanek/pg/model/PgContext.java Line 32 in 439a77d
Then you need to add a new method to QueryExecutors pg-index-health/pg-index-health/src/main/java/io/github/mfvanek/pg/utils/QueryExecutors.java Line 77 in 439a77d
|
See article https://habr.com/ru/articles/800121/
The text was updated successfully, but these errors were encountered: