jBPM database size with PostgreSQL

[akoskm]

I trying to change an application to have support for persistence and currently I'm looking at the db scheme what the engine creates.

I came across this particular table called sessioninfo, it has a column called rulesbytearray which doesn't seem like a bytearray but some kind of an id.

My assumption is that this column references the rows from pg_catalog pg_largeobject table, for some reason ...

The question is, why there isn't another column in sessioninfo holding the actual bytearray instead of referencing a table in pg_catalog

Currently we're also experiencing problems with this table, the records are never deleted and after a few days of testing human tasks,

the tables in the public schema are nearly empty but the whole jbpm database uses 42GB disk space.

Do you have any idea why is this happening?

Who creates those largeobject entries and why?

Currently the only way to cleanup this table is to do vacuumlo then vacuum.

PostgreSQL 9.3

jbpm 6.1.0.Final

Here is the output of vacuumlo https://gist.github.com/akoskm/91e6082f04fc0e4e9deb

[swiderski.maciej]

this seems to be an issue with postgresql that is handling blobs in two ways - oid or bytea and details you can find here. So I would recommend to apply this changes on db level - change data type for columns that are oid now to bytea and set the additional parameter in persistence.xml to see if that solves the problem. You can make use of ddl scripts that are shipped with jbpm installer.

HTH

[akoskm]

Thanks for the reply.

I don't see how this is an issue with PostgreSQL, looking at the create script, this is how you designed it:

create table ProcessInstanceInfo (
  InstanceId int8 not null,
  lastModificationDate timestamp,
  lastReadDate timestamp,
  processId varchar(255),
  processInstanceByteArray oid,
  startDate timestamp,
  state int4 not null,
  OPTLOCK int4,
  primary key (InstanceId)
  );

which would be fine for us if it wouldn't leak disk space. We can do the suggested changes but I think this is an issue from your side that needs to be fixed. Should I open a ticket for this?

Currently we aren't using any persistence xml, our setup looks like:

        pds = new PoolingDataSource();
        pds.setUniqueName("jdbc/jbpm-ds");
        pds.setClassName("bitronix.tm.resource.jdbc.lrc.LrcXADataSource");
        pds.setMaxPoolSize(5);
        pds.setAllowLocalTransactions(true);
        pds.getDriverProperties().put("user", "jbpm6");
        pds.getDriverProperties().put("password", "jbpm6");
        pds.getDriverProperties().put("url", "jdbc:postgresql://localhost/jbpm6");
        pds.getDriverProperties().put("driverClassName", "org.postgresql.Driver");
        pds.init();
     
        Properties props = new Properties();
        props.setProperty("user", "jbpm6");
        props.setProperty("password", "jbpm6");
        props.setProperty("url", "jdbc:postgresql://localhost/jbpm6");
        props.setProperty("driverClassName", "org.postgresql.Driver");
        props.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
        emf = Persistence.createEntityManagerFactory("org.jbpm.persistence.jpa", props);

I can change the datatype of the column on db level but which additional parameters should be set and where if we have no persistence.xml?

[akoskm]

I was able to override the column type from persistence.xml:

<mapping-file>META-INF/orm.xml</mapping-file>

here is how my orm.xml looks like:

<?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 http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
                 version="2.0">
    <description>Workaround for PostgreSQL and JPA annotation type @Lob</description>
    <package>org.jbpm.persistence.processinstance</package>
    <entity class="ProcessInstanceInfo" name="ProcessInstanceInfo" access="FIELD">
        <table name="processinstanceinfo"/>
        <attributes>
            <basic name="processInstanceByteArray" access="FIELD">
                <column name="processInstanceByteArray" column-definition="bytea"/>
            </basic>
        </attributes>
    </entity>
</entity-mappings>

[swiderski.maciej]

thanks for sharing this!

I guess you can mark the question as answered/assumed answered so others might benefit while searching.