Skip to content
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

Support PostgreSQL JSONB data type in JdbcEventStorageEngine #1067

Open
sgrimm-sg opened this issue Apr 22, 2019 · 2 comments

Comments

Projects
None yet
2 participants
@sgrimm-sg
Copy link

commented Apr 22, 2019

JdbcEventStorageEngine doesn't easily support using nonstandard data types such as PostgreSQL's JSONB for the payload and metadata columns.

The problem is that the queries pass the output of the serializer to the JDBC driver with PreparedStatement.setObject(), but there's no way to communicate type information.

In the case of PostgreSQL's JDBC driver, you can insert into a JSONB column in either of two ways. First, you can add an explicit cast to the prepared statement, e.g.,

PreparedStatement ps = conn.prepareStatement("INSERT INTO foo (bar) VALUES (?::JSONB)");
ps.setObject(1, jsonFromSerializer);

Or you can pass the value as a PGobject with type information.

PreparedStatement ps = connection.prepareStatement("INSERT INTO foo (bar) VALUES (?)");
PGobject pgObject = new PGobject();
pgObject.setType("JSONB");
pgObject.setValue(jsonFromSerializer);
ps.setObject(1, pgObject);

Either approach could be exposed as a public API by JdbcEventStorageEngine. Maybe something along the lines of one of

storageEngineBuilder
    .payloadBindString("?::JSONB")
    .metaDataBindString("?::JSONB")

or

private PGobject wrapInPgObject(Object data) {
    PGobject pgObject = new PGobject();
    pgObject.setType("JSONB");
    pgObject.setValue(data);
    return pgObject;
}

...
storageEngineBuilder
    .payloadConverter(this::wrapInPgObject)
    .metaDataConverter(this::wrapInPgObject);
@smcvb

This comment has been minimized.

Copy link
Member

commented May 7, 2019

Hi @sgrimm-sg, good point you're having here.
I've had the 'pleasure' of getting a JpaEventStorageEngine work nicely with JSONB, but not yet with the JdbcEventStorageEngine.

We feel that there are some improvements to be made in the framework to make adjusting the payload and meta data types easier on reading and writing. However, I think you can manage with the current set up as well.

The JdbcEventStoragenEngine.Builder has a dataType(Class<?>) method. If you'd change the default dataType from byte[] to PGobject, we have the first step on this approach ready.
Secondly, the chosen Serializer should be adjusted to cope with the PGobject, as we've changed the dataType used upon serializing your events.
To do that, a ContentTypeConverter (Axon Framework specific interface) for PGobject should be provided. If you're using the JacksonSerializer I'd assume a PGobjectToJsonNodeConverter, and vice versa, would make sense.

The only unknown I have in the above described process, is whether the JdbcEventStorageEngine#readPayload(ResultSet, String) on line 659 would cope when reading a PGobject.

This also brings me to the improvement I talked about earlier.
The readPayload(ResultSet, String) method is there to provide users with the option to adjust how to read a payload/metadata field from the database.

Taking your earlier builder suggestion, I think we should allow users to specify the entire readPayload(ResultSet, String) method in the Builder. Additionally, there should also be a possibility to adjust how to write payload. This would of course require an adjustment in the JdbcEventStorageEngine.

So, that's my current suggestion to you @sgrimm-sg, paired with a feature suggesting for the JdbcEventStorageEngine. Would you view the suggestion to be sufficient?

@smcvb smcvb self-assigned this May 7, 2019

@sgrimm-sg

This comment has been minimized.

Copy link
Author

commented May 8, 2019

That suggestion seems like it might do the trick, yes. Obviously we'll need to try it to make sure, but it sounds reasonable.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.