Axonframework: Over 1000 gaps triggers Oracle "1000 elements in list" limitation

[ericjanmalotaux]

The number of gaps in the events processed by a tracking event processor occasionally exceeds 1000, which triggers the Oracle limitation of maximum number of elements in an IN-clause. We would like a work-around in Axon Framework to handle this situation by splitting the IN-clause in as many IN-clauses as needed, concatenated by OR's.
We think the following code in org.axonframework.eventsourcing.eventstore.jdbc.JdbcEventStorageEngine (version 4.0.3) would do this:

    protected PreparedStatement readEventDataAxon(Connection connection, TrackingToken lastToken,
        int batchSize) throws SQLException {
        isTrue(lastToken == null || lastToken instanceof GapAwareTrackingToken,
            () -> format("Token [%s] is of the wrong type", lastToken));
        GapAwareTrackingToken previousToken = (GapAwareTrackingToken) lastToken;
        String sql = "SELECT " + trackedEventFields() + " FROM " + schema.domainEventTable() +
            " WHERE (" + schema.globalIndexColumn() + " > ? AND " + schema.globalIndexColumn() + " <= ?) ";
        List<Long> gaps;
        if (previousToken != null) {
            gaps = new ArrayList<>(previousToken.getGaps());
            if (!gaps.isEmpty()) {
//                sql += " OR " + schema.globalIndexColumn() + " IN (" +
//                    String.join(",", Collections.nCopies(gaps.size(), "?")) + ") ";
                final AtomicInteger counter = new AtomicInteger(0);
                sql += " OR " + Collections
                    .nCopies(gaps.size(), "?")
                    .stream()
                    .collect(Collectors.groupingBy(it -> counter.getAndIncrement() / 1000))
                    .values()
                    .stream()
                    .map(placeholders -> placeholders.stream().collect(Collectors.joining(", ", schema.globalIndexColumn() + " IN (", ")")))
                    .collect(Collectors.joining(" OR "));
            }
        } else {
            gaps = Collections.emptyList();
        }
        sql += "ORDER BY " + schema.globalIndexColumn() + " ASC";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long globalIndex = previousToken == null ? -1 : previousToken.getIndex();
        preparedStatement.setLong(1, globalIndex);
        preparedStatement.setLong(2, globalIndex + batchSize);
        for (int i = 0; i < gaps.size(); i++) {
            preparedStatement.setLong(i + 3, gaps.get(i));
        }
        return preparedStatement;
    }

The same solution would be applicable to version 3.x.

[smcvb]

@ericjanmalotaux, ideally, you wouldn't have that many gaps to begin with between your events. We recommend users to have a _dedicated_ sequence generator for the global index on your events.

The gaps-logic in the framework is meant to be able to cover the case where events come out of order, to ensure they're being handled. Having more than a 1000 gaps between your events is likely not due to events coming out of order, or at least, I'd assume so.

So, concluding, have you tried specifying a dedicated sequence generator for your domain_event_entry table?

[ericjanmalotaux]

We already have a dedicate sequence generator for our events table. Still it is not possible to avoid >1000 gaps at all times. Some commands generate more than 1000 events. Another command could generate less events and commit earlier, causing out-of-order events, or the _big_ command could be rollbacked, causing a permanent gap.

I agree that it would be better to have less events per command, but that will take time. Also we could introduce AxonServer, which also takes time. In the meantime removing this arbitrary and undocumented limit would help.

[smcvb]

Thanks for the back ground @ericjanmalotaux,

I'll ensure we discuss this idea here, but I am hard pressed to give you a time frame on this. The suggested fix should work, but I feel we'd need to take more possibilities into account in regards to specific databases. This will take some time, possibly time you do not have.

In the mean time, you could override the JdbcEventStorageEngine and override the function to cover the scenario you're having here. Specifying your CustomJdbcEventStorageEngine is relatively easy, by building your own EmbeddedEventStore with the Builder pattern in place, using the EmbeddedEventStore.Builder#storageEngine(EventStorageEngine) function to select your own.

Hope this gets you going for now and thanks for filing this suggestion.

[ericjanmalotaux]

Thanks @smcvb, that's exactly what we are doing. At the same time we wanted to be good open source community citizens and offer our solution to whoever would benefit from it.

[smcvb]

That is very great to hear, thank a lot @ericjanmalotaux!

I pointed this out a little, but let me be a bit more specific; we very, very much value the contributions from the users of the framework.
Doing this exact thing right here is one of the things that keeps us going.
So, if you ever have other thoughts/ideas/suggestions regarding Axon Framework, please share them with everybody! :-)

[abuijze]

This issue has been the subject of discussions several times, here. On one hand, we do see there are some optimizations that could be done in the framework. However, we feel that the root cause is in the occurrence of the high number of gaps, in the first place. Configuring the database to work with dedicated sequences for the Domain Event Entry table is a solution that targets the root cause.
Ultimately, there is always the possibility to tweak the exact SQL statements performed by the Storage Engine to resolve database-specific issues that may arise.

Since there are enough alternatives, we have decided not to build anything specific into Axon to resolve this issue.