Re: [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL

On Thu, Aug 30, 2012 at 3:33 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:

> Gary Webster wrote:
> > The subject says most of what I know at this point.
> >
> > We are still not getting along with Apache Jackrabbit.
> > After a few hours of using Postgres as the Persistence Manager, the
> JCR gets stuck, apparently on a
> > simple DB update statement.
> >
> > This problem does not occur at all if we substitute MySQL!
> >
> > This is Postgres v9.1.3 on RHEL5, 64-bit.
>
> If you want to know if there are locking issues
> in the database, examine pg_locks and pg_stat_activity.
>
> If you use MyISAM in MySQL, it is not surprising that
> you have no locking issues, since no effort is made to
> ascertain data integrity.
>
> Yours,
> Laurenz Albe
>

Hello.
Thanks for the info.

I responded about pg_stat_activity in my other post.
The only interesting activity I see is this:
"update JOURNAL_LOCAL_REVISIONS set REVISION_ID = $1 where JOURNAL_ID = $2"

I don't know exactly what I'm looking for in pg_locks .

MySQL works OK with both MyISAM & InnoDB.

Here is some info from the JCR side:
I see these two log statements with Postgres and MySQL in which case they
both halt...
INFO - http-8080-35 - org.apache.jackrabbit.core.journal.AbstractJournal -
Record with revision '20942' created by this journal, skipped.
INFO - http-8080-35 - org.apache.jackrabbit.core.journal.AbstractJournal -
Synchronized to revision: 20942
Then, MySQL 'breaks free' but Postgres does not.

We are using JDBC, though I'm not sure where I should be getting that
from...

version: PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
This install came from EnterpriseDB package.
The hardware has 8 CPU cores, & 12GB RAM.
I am using autovacuum, with "autovacuum_vacuum_cost_limit = 500" .