Axon PostgreSQL without TOAST (Having issues)

I found the blog post to bypass the use of OID and rather store inline: https://blog.trifork.com/2017/10/09/axon-postgresql-without-toast/

Problem is, I am seeing the following error when I try to follow the documentation from the site:

Caused by: org.springframework.beans.factory.BeanCurrentlyInCreationException: Error creating bean with name ‘axonEntityManagerFactory’: Requested bean is currently in creation: Is there an unresolvable circular reference?

I am setting my dialect within my application.properties as so: spring.jpa.properties.hibernate.dialect = com.ge.energy.markets.bids.demand.eventsourcing.db.AxonPostgreSQLDialect

This is how I am creating my axonEntityManagerFactory bean (the only difference is that I pass in the JpaVendorAdapter, as I figure this is preconfigured by Spring?):

@Bean(name = “axonEntityManagerFactory”)

@Primary

@Lazy

public LocalContainerEntityManagerFactoryBean axonEntityManagerFactory(

DataSource dataSource,

@Qualifier(“jpaProperties”) Properties jpaProperties,

JpaVendorAdapter jpaVendorAdapter) {

final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

em.setDataSource(dataSource);

em.setPackagesToScan(

“org.axonframework.eventsourcing.eventstore.jpa”,

“org.axonframework.eventhandling.saga.repository.jpa”,

“org.axonframework.eventhandling.tokenstore.jpa”);

em.setJpaVendorAdapter(jpaVendorAdapter);

em.setJpaProperties(jpaProperties);

em.setMappingResources("/orm.xml");

return em;

}

Please help, as I seem to be unable to read any events, seeing as these objects are stored as LOB objects and I never seem to be able to pull them down. I am going through this trouble as I just want to pull down all events, from my PostgreSQL DB, based on mrid…

I got past my initial issue – problem I am having now is that although the table has changed to bytea, the event storage still seemingly tries to store the entry as a bigint for OID, rather than storing the payload and metadata as bytea values…

The only difference between my implementation, and that of the blog post is that I do not pass through the jpaProperties as it cannot find that bean (I believe it is because I am using an application.properties file)

Here is my bean:

@Bean(name = “axonEntityManagerFactory”)

@Primary

public LocalContainerEntityManagerFactoryBean axonEntityManagerFactory(

DataSource dataSource) {

HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();

jpaVendorAdapter.setGenerateDdl(true);

jpaVendorAdapter.setShowSql(true);

final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();

em.setDataSource(dataSource);

em.setPackagesToScan(

“org.axonframework.eventsourcing.eventstore.jpa”,

“org.axonframework.eventhandling.saga.repository.jpa”,

“org.axonframework.eventhandling.tokenstore.jpa”);

em.setJpaVendorAdapter(jpaVendorAdapter);

em.setMappingResources("/orm.xml");

return em;

}

I was able to get it running by initializing my own JpaProperties, but I figure there has to be a way to create a Properties object from my existing application.properties file

here is what I did:

Properties jpaProperties = new Properties();

jpaProperties.put(“hibernate.dialect”,“com.ge.energy.markets.bids.demand.eventsourcing.db.AxonPostgreSQLDialect”);

Could anyone please shed some light on how I could get the properties into the Properties object, from my application.properties file?

Since I seem to be the only one talking to myself… :smiley: – Has anyone been able to replicate this feat for jsonb types in PostgreSQL? The columns generate OK, but I’m struggling with the SQLDialect for jsonb…

I tried utilizing this link to help, to utilize this jsonbinary type descriptor, but to no avail: https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/

HI Robert,

I have checked with one of our clients. They are using JSON fields to store payloads, and have done a few ORM overrides to make it work.

They have the following META-INF/orm.xml file:

<?xml version="1.0" encoding="UTF-8" ?>

You should only need to override the entities for which you’re planning to store JSON.
Hope this helps.

Cheers,

Allard

Hi Allard, I attempted this – the issue lies in issuing an event, as the AxonPostgreSQLDialect class used for the format above follows an BinaryTypeDescriptor.INSTANCE and is unable to process JSON values.

Do you know if your client has a specialized class extending the PostgreSQLDialect, for JSON? Here is an example of my dialect class:

import java.sql.Types;

import org.hibernate.dialect.PostgreSQL94Dialect;

import org.hibernate.type.descriptor.sql.BinaryTypeDescriptor;

import org.hibernate.type.descriptor.sql.SqlTypeDescriptor;

public class AxonPostgreSQLDialect extends PostgreSQL94Dialect {

public AxonPostgreSQLDialect() {

super();

System.out.println("---------Registering Axon PostgreSQLDialect---------");

this.registerColumnType(Types.BLOB, “BYTEA”);

}

@Override

public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {

if (sqlTypeDescriptor.getSqlType() == java.sql.Types.BLOB) {

return BinaryTypeDescriptor.INSTANCE;

}

return super.remapSqlTypeDescriptor(sqlTypeDescriptor);

}

}

They use a custom dialect similar to the one described in this blog post.

If you define your column types as bytea, does it then work?

Allard

Yessir it does. I have verified with BYTEA, but when I change to JSON - I am never able to get events to store properly. I tried using the Descriptor in this blog, but it didn’t work out on my end. Hibernate does not have a descriptor built in for json, as not all databases provide functionality for this datatype – only postgreSQL and mySQL.

https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/

I’m just trying to figure out what the correct Dialect modification would be – I used the post you sent in order to set up BYTEA, initially.

Following up on what I said earlier, are they utilizing a json or jsonb field? I attempted it with jsonb

Allard, were you able to get any more context on this?

Hi Robert,

The client Allard’s referring to is the client I am helping out at.

Additionally, that blog post was written by an old colleague of ours, for this exact same client.

What’s written in the blogpost is thus what’s happening at client.

To provide you with more specifics, this is an example impl of that dialect being used:

public class SpecificPostgreSQLDialect extends PostgreSQL94JsonDialect {

    private static final String BYTEA = "BYTEA";

    public SpecificPostgreSQLDialect() {
        super();
        this.registerColumnType(BLOB, BYTEA);
    }

    @Override
    public SqlTypeDescriptor remapSqlTypeDescriptor(SqlTypeDescriptor sqlTypeDescriptor) {
        return sqlTypeDescriptor.getSqlType() == BLOB
                ? BinaryTypeDescriptor.INSTANCE
                : super.remapSqlTypeDescriptor(sqlTypeDescriptor);
    }
}

Additionally, we’ve also set an orm.xml exactly like Allard shared it.

As you can see, this is through BYTEA.

I wouldn’t know why json or jsonb isn’t working as expected in your scenario.

Added though, I feel this isn’t an Axon specific problem anymore…

Have you tried figuring out how to forcefully adjust a column by querying through other channels?

Or, might you already have figured out how to get the column type to json/jsonb?

Cheers,

Steven

Hi robert
I am a programmer from China.
I tied to use axon with postgresql, and store event ,saga,token, snapshot in json or jsonb.
The attachment is my demo. In this project i rewrite some beans like:

com.mingrui.base.axon.pg.PgAssociationValueEntry
com.mingrui.base.axon.pg.PgDomainEventEntry
com.mingrui.base.axon.pg.PgJpaEventStorageEngine
com.mingrui.base.axon.pg.PgJpaSagaStore
com.mingrui.base.axon.pg.PgJpaTokenStore
com.mingrui.base.axon.pg.PgSagaEntry
com.mingrui.base.axon.pg.PgSerializedSaga
com.mingrui.base.axon.pg.PgSnapshotEventEntry
com.mingrui.base.axon.pg.PgTokenEntry

and then i config axon use com.mingrui.saas.config.AxonConfig,the project will create tables startwith “pg” to replace the default tables of axon in jpa,like “pg_association_value_entry”.
Then, i copy the code of JpaTokenStore to PgJpaTokenStore,and JpaSagaStore to PgJpaSagaStore and JpaEventStorageEngine to PgJpaEventStorageEngine.Then i rewrite some code in this three beans,to let the framework work with the new tables which startwith 'pg".
I test the event,saga and snapshot,all of them works well. You can use override and extend to make your code better,not just copy and rewrite.

BTW, i just use JacksonSerializer to do this,if you want it work with other serializer,you should rewrite getMetaData getPayload in PgDomainEventEntry and PgSnapshotEventEntry,also you need to rewrite PgSerializedSaga in PgSerializedSaga too.

Hope it helpful for you.

在 2018年8月7日星期二 UTC+8上午4:48:12,Robert Delgado写道:

journey1989-mingrui-software-master.zip (89.7 KB)

Thanks everyone! So in short can we conclude that oid in postgres is not supported for tracking precessors?

If that’s the case it would be nice to add it as a disclaimer in the docs.

If that’s not the case then does anyone have an example app that i can look at this running?

Hi Guido,

We are planning a section to describe some database caveats (as documented in this issue).

I’ll ensure you this topic about PostgreSQL not playing nice with blobs is going to be a part of that.

I would like to state though that it is not a matter of not being supported.

The framework works just fine with the payload and metadata being stored as an OID column.

For debugging purposes it is just completely awful to look at…

Thus in short, your conclusion is wrong, it works just fine, it is just not very practical.

Cheers,
Steven

Thanks Steven!

Any pointers to reference implementation or examples that make use of tracking event handlers?

Hi Guido,

Assuming your request is how to use Tracking Event Processor and how to configure them, I’d suggest you take a look at the reference guide.

If your question is about the combination of what this thread is actually about (overriding OID columns to BYTEA columns when using PostgreSQL, I’d like to point to the blog post shared by Robert at the beginning of this thread.

Any how, mixing both Tracking Event Processors and the usage of PostgreSQL does not hold any specific magic.

Thus I’d suggest you regard those as distinct topics.

Cheers,
Steven

Thanks for the clarification!

Hi Steven. Sorry for hijacking this thread.
I strongly disagree with your statement: “Thus in short, your conclusion is wrong, it works just fine, it is just not very practical.”
It’s not just very unpractical, it’s a bug (database memory leak) in your system. When you use Postgres as an event store, token_entry table stores the token position also as a large object. When when it advances its position, the old large object is never removed! This pollutes the database with tons of large objects!
I believe that you should have made this clear before claiming that “The framework works just fine with the payload and metadata being stored as an OID column”.
Having such a serious memory leak is not fine at all.

Hi Daniel,

No worries, you’re not hijacking this thread at all!

Your comment seems more than valid as being a part in here.

To be completely honest with you, I wasn’t aware PostgreSQL does not remove the objects referenced in the OID.
If I would’ve known that, I wouldn’t have resolved this thread with the statement as I did.
For that, I am tremendously sorry!

However, I also would like to thank you that you’re pointing this out; this is definitely a bug problem.
Firstly though, would you be able to provide the source which states that if an OID reference is dropped, that the Large Object is remained in the database?
From there on out, I think we at AxonIQ should be able to provide the correct guidance in respect to this topic.

By the way, there is one misconception in your argument.
Using PostgreSQL as your Event Store in an Axon based application does not in any way enforce you to use it as a Token Store as well.
It is definitely true the auto configuration will assume this route, especially in a Spring Boot situation, but you’r entirely free to have different databases for both.
When using Axon Server for example you’d typically have it as the Event Store, but you’d still need a Token Store in your own application (as currently Axon Server is not intended as a Token Store, although we are debating this topic).

Concluding, I like to state we very much value your input Daniel!
I am looking forward to your resources regarding the maintaining of PostgreSQL objects which are stored through a OID.

Cheers,

Steven van Beelen

Axon Framework Lead Developer

AxonIQ

Steven, thank you for your detailed response.
I do agree that this bug is more related to the token store than the event store.
Anyway, here is some more input.
The fact that large objects (those that are referenced by OIDs) are not deleted automatically - I verified myself manually. Also see this and this questions on stackoverflow.
In general, maintaining large objects in Postgres is a pain in the ass: you have to periodically clean them with vacuumlo utility.

To be honest I believe that using OIDs was not a good choice in the first place: byte array or even string representation of token/event payload could be better.