Axon PostgreSQL without TOAST (Having issues)

[Robert Delgado]

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.....

[Robert Delgado]

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;

}

[Robert Delgado]

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?

[Robert Delgado]

Since I seem to be the only one talking to myself... :D   -- 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/

[Allard Buijze]

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" ?>

<entity-mappings

        xmlns="http://java.sun.com/xml/ns/persistence/orm"

        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd"

        version="2.0">

    <entity class="org.axonframework.eventsourcing.eventstore.jpa.DomainEventEntry">

        <attribute-override name="payload">

            <column name="payload" column-definition="BYTEA"/>

        </attribute-override>

        <attribute-override name="metaData">

            <column name="metadata" column-definition="BYTEA"/>

        </attribute-override>

    </entity>

    <entity class="org.axonframework.eventsourcing.eventstore.jpa.SnapshotEventEntry">

        <attribute-override name="payload">

            <column name="payload" column-definition="BYTEA"/>

        </attribute-override>

        <attribute-override name="metaData">

            <column name="metadata" column-definition="BYTEA"/>

        </attribute-override>

    </entity>

    <entity class="org.axonframework.eventhandling.saga.repository.jpa.SagaEntry">

        <attribute-override name="serializedSaga">

            <column name="serializedsaga" column-definition="BYTEA"/>

        </attribute-override>

    </entity>

    <entity class="org.axonframework.eventhandling.tokenstore.jpa.TokenEntry">

        <attribute-override name="token">

            <column name="token" column-definition="BYTEA"/>

        </attribute-override>

    </entity>

</entity-mappings>

You should only need to override the entities for which you're planning to store JSON.

Hope this helps.

Cheers,

Allard

On Tue, 7 Aug 2018 at 23:13 Robert Delgado <robel...@gmail.com> wrote:

Since I seem to be the only one talking to myself... :D   -- 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/

--
You received this message because you are subscribed to the Google Groups "Axon Framework Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Allard Buijze
CTO

E: allard...@axoniq.io
T: +31 6 34 73 99 89

[Robert Delgado]

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);

[Allard Buijze]

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

[Robert Delgado]

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.

[Robert Delgado]

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

[Robert Delgado]

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

[Steven van Beelen]

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

On Thu, Aug 9, 2018 at 3:11 PM Robert Delgado <robel...@gmail.com> wrote:

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

[wudan198...@gmail.com]

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写道：

[Guido de Caso]

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?

--
You received this message because you are subscribed to the Google Groups "Axon Framework Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Guido de Caso

[Steven van Beelen]

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

[Guido de Caso]

Thanks Steven!

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

[Steven van Beelen]

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

[Guido de Caso]

Thanks for the clarification!

[Daniel Gregor]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to axonfr...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Guido de Caso

--
You received this message because you are subscribed to the Google Groups "Axon Framework Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to axonfr...@googlegroups.com.

[Steven van Beelen]

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

 

To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.

[Daniel Gregor]

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.

[Allard Buijze]

Hello Daniel,

thanks for your feedback. Unfortunatey, it wasn’t an actual choice on our side. This is Hibernate/the Postgres dialect choosing to store a byte[] as an OID.

For the event store, that’s generally not an issue, as objects are created once and left alone. The TokenStore chanfes continuously. 

We have been sharing some information on this list, I believe, about best practises on Postgres, providing some example configuration to ‘persuade’ Hibernate to store these fields as bytea instead of OID. 

Our friends at Trifork have written a blog about it too: 

https://blog.trifork.com/2017/10/09/axon-postgresql-without-toast/

Cheers,

Allard

To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

[Daniel Gregor]

Hi Allard,

I already checked out the link, and the workaround described there actually works. 

However, it would be better if it was a part of Axon framework itself, for example configurable by some properties.

Otherwise like I said it's a memory leak. And even if we use Axon Server, we'll still need Postgres as a token store. From a mature framework I don't expect to have such memory leaks.

Best,

Daniel

[Allard Buijze]

Hi Daniel,

we could debate whether it’s a memory leak or just garbage creation. I’d argue it’s the latter. 

We’d love to have a simple property, or better, do it based on dialect. The challenge is to do it in a way that doesn’t break compatibility with current solutions. If you have any suggestions, we open to hearing them. 

Cheers,

Allard

To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[johnny....@gmail.com]

Hi,

i use PostgeSQL column type "jsonb" for "METADATA" and "PAYLOAD" columns via JPA.

My "orm.xml" for PostgreSQL contains:

        <mapped-superclass class="org.axonframework.eventsourcing.eventstore.AbstractEventEntry">

               <attributes>

                       <basic name="payload" optional="false">

                               <column column-definition="jsonb" length="1000" />

                               <convert converter="org.tryout.infrastructure.eventsourcing.axon.postgresql.JpaPostgreSqlJsonbBytearrayConverter" />

                       </basic>

                       <basic name="metaData" optional="false">

                               <column column-definition="jsonb" length="1000" />

                               <convert converter="org.tryout.infrastructure.eventsourcing.axon.postgresql.JpaPostgreSqlJsonbBytearrayConverter" />

                       </basic>

               </attributes>

       </mapped-superclass>

The converter looks like this:

@Converter(autoApply = false)

public class JpaPostgreSqlJsonbBytearrayConverter implements AttributeConverter<byte[], Object> {

       @Override

       public String convertToDatabaseColumn(byte[] bytes) {

               return (bytes != null) ? new String(bytes, StandardCharsets.UTF_8) : null;

       }

       @Override

       public byte[] convertToEntityAttribute(Object json) {

               return (json != null) ? json.toString().getBytes(StandardCharsets.UTF_8) : null;

       }

}

This is not guaranteed to be the best or fastest way to do it. I even don't know, if there is a memory leak (never experienced it).

But it uses "jsonb", which has some advantages with json data.

I also wanted to do it JPA-provider-independent (as Converter). This is always advisable.

Cheers. Johnny

[Daniel Gregor]

Allard,

The way you call it doesn't matter: memory leak, garbage creation, leftover objects, etc. The outcome is the same.

Anyways, I understand that the solution has to be backwards compatible. Hence I would suggest to add this trigger property with a default value to work the "old" way. This way at least for new users there would be a chance to avoid this "garbage creation", assuming that it's well documented somewhere.

Johnny,

The problem is not in the event store ("domain_event_entry" table), it's in the token store ("token_entry" table). The token in stored in the "token" field of the "token_entry" table. It's basically a serialised XML object. And when the token advances its position, the old serialised object never gets deleted. Hope this helps.

[Steven van Beelen]

Hi all,

Very worthwhile conversation we're having here, with lot's of good info contained in it for our users.

Daniel, could I ask a favor of you?

As I feel you have quite a thorough understanding of the problem at hand in regards to PostgreSQL and feeling of how to resolve this, would you be up for adding an issue to the issue tracker?

I'd hate to lose the conversation in this user group, whilst you're obviously looking to improve the framework for everybody through your comments.

Cheers,

Steven

--

[Herr Manns]

Hi!

Same problem here for us. I liked daniels Idea of having a switch to get a better behaviour for at least new users.
Is there already an issue you can point to?

Thanks,

Dirk 

[Steven van Beelen]

Hi Dirk,

I haven't heard anything from Daniel after my request to introduce an issue on this point.

So sorry Dirk, no, there is no issue on the board for this yet.

Thus for now, I would suggest to follow the blog post Allard shared on the topic, as that has been the most pragmatical solution for all Axon users up to now.

For clarity, I am talking about this blog post.

Hope this helps!

Cheers,

Steven

--

You received this message because you are subscribed to the Google Groups "Axon Framework Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to axonframewor...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/axonframework/02796ded-09ae-46de-9a95-5822f24f6933%40googlegroups.com.

[Joel]

Revisiting this thread and trying to accomplish the same task.  I'm using v4.2 of Axon Framework (Spring Boot Starter) w/ postgres 12.  I've attempted to use Johnny's solution using a converter, which makes the most sense to me as it leaves BLOBs intact for other purposes.  The insert is not reaching the server, and I'm receiving:

org.postgresql.util.PSQLException: Unsupported Types value: 792,791,759

which is perhaps a result of:

xyz_1  | 2019-11-10 18:09:30.698 DEBUG 1 --- [   scheduling-1] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : [B@7820b8e0 -> {"traceId":"4a62c7c4-977d-4a0a-b062-5f691ad1c666","correlationId":"4a62c7c4-977d-4a0a-b062-5f691ad1c666"}
xyz_1  | 2019-11-10 18:09:30.698 TRACE 1 --- [   scheduling-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [UNKNOWN(792791759)] - [{"traceId":"4a62c7c4-977d-4a0a-b062-5f691ad1c666","correlationId":"4a62c7c4-977d-4a0a-b062-5f691ad1c666"}]

My converter (in Kotlin) looks like:

@Suppress("unused")
@Converter(autoApply = false)
class JpaPostgreSqlJsonbBytearrayConverter : AttributeConverter<ByteArray, Any> {
    private val mapper = ObjectMapper()

    override fun convertToDatabaseColumn(bytes: ByteArray?): String? {
        return if (bytes != null) String(bytes, StandardCharsets.UTF_8) else null
    }

    override fun convertToEntityAttribute(json: Any?): ByteArray? {
        return json?.toString()?.toByteArray(StandardCharsets.UTF_8)
    }

    companion object : KLogging()
}

I was considering that it may have something to do with the Kotlin representation of Strings and Hibernate not knowing how to map them.  I tried explicitly returning java.lang.String, but this also failed, and I think it's the same under the hood.

The bytea representation described in the attached article worked perfectly, and is a big step up from the BLOB representation, but I would like my events to be in jsonb.

Is there an updated solution for Axon 4.2?  Thanks!

On Friday, August 2, 2019 at 10:13:10 AM UTC-4, Steven van Beelen wrote:

Hi Dirk,

I haven't heard anything from Daniel after my request to introduce an issue on this point.

So sorry Dirk, no, there is no issue on the board for this yet.

Thus for now, I would suggest to follow the blog post Allard shared on the topic, as that has been the most pragmatical solution for all Axon users up to now.

For clarity, I am talking about this blog post.

Hope this helps!

Cheers,

Steven

On Thu, Aug 1, 2019 at 2:42 PM Herr Manns <h3rm...@gmail.com> wrote:

Hi!

Same problem here for us. I liked daniels Idea of having a switch to get a better behaviour for at least new users.
Is there already an issue you can point to?

Thanks,

Dirk 

--
You received this message because you are subscribed to the Google Groups "Axon Framework Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to axonfr...@googlegroups.com.