IDP V3: LockTimeoutException with remote PG DB

[Walter Forbes Hoehn (wassa)]

Here is my table description:

          Table "public.storagerecords"
   Column   |          Type          | Modifiers 
------------+------------------------+-----------
 context    | character varying(255) | not null
 key        | character varying(255) | not null
 expiration | bigint                 | 
 value      | text                   | not null
 version    | bigint                 | not null
Indexes:
    “storagerecords_pkey" PRIMARY KEY, btree (context, key)

Here is the “vacuumlo -vn” output:

Connected to idp
Test run: no large objects will be removed!
Would remove 22 large objects from idp.

At the time I ran vacuumlo, there were 681 rows in the storagerecords table. Looking in the pg_largeobject table, I notice that all of the values are for my personal account and have timestamps from 3/27.  I’m guessing these entries are, in my particular configuration, a red herring and arose from my early configuration experiments. After running "vacummlo -v” no further entries appear.

Another perhaps unique thing about my configuration is that I have a custom ORM, which I’ve included below.  Most people shouldn’t need this, but I was running V3 very early before some of the default table names were changed.


<?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="1.0">
  <package>org.opensaml.storage.impl</package>

  <!-- changes the table name and column names -->
  <entity class="JPAStorageRecord" access="PROPERTY">
    <table name="storagerecords"/>
    <id-class class="JPAStorageRecord$RecordId"/>
    <attributes>
      <id name="context">
        <column name="context" nullable="false"/>
      </id>
      <id name="key">
        <column name="key" nullable="false"/>
      </id>
      <basic name="value">
        <column name="value" nullable="false"/>
      </basic>
      <basic name="expiration">
        <column name="expiration" nullable="true"/>
      </basic>
      <basic name="version">
        <column name="version" nullable="false"/>
      </basic>
    </attributes>
  </entity>
  <embeddable class="JPAStorageRecord$RecordId" access="PROPERTY">
    <attributes>
      <basic name="context">
        <column name="context" nullable="false"/>
      </basic>
      <basic name="key">
        <column name="key" nullable="false"/>
      </basic>
    </attributes>
  </embeddable>
</entity-mappings>


> On Oct 20, 2015, at 9:45 AM, Etienne Dysli-Metref <etienne.dysli-metref at switch.ch> wrote:
> 
> On 13/10/15 15:40, Walter Forbes Hoehn (wassa) wrote:
>> For what it’s worth (probably very little), we have been running with
>> JPA storage using Postgres for several months.  We’ve encountered no
>> problems, either in production or during synthetic load testing.
> 
> Maybe it's worth more than you think to us... :)
> 
> Do you mind posting the output of "\d storagerecords"?
> What happens if you run "vacuumlo -vn"? Does it want to remove as many
> large objects as you have rows in storagerecords?
> 
> I'm asking because we (SWITCH) are starting to think that the type
> "text" that is assigned by Hibernate to the storagerecords.value column
> is wrong and should be "oid" instead...
> 
> With Hibernate + Postgresql, we've observed that a large object
> identifier gets stored in the value column and the actual content ends
> up in the pg_largeobject table. Trouble is, that large object is
> immediately orphaned upon creation because a "text" type is not a large
> object reference, so Postgresql and vacuumlo think all such large
> objects are orphaned. The IdP still works, apparently the JDBC driver
> looks up the "fake" oids, but updated/deleted values accumulate in the
> pg_largeobject table and won't be cleaned up (and if you run vacuumlo it
> deletes every object and your values are gone). We tried to work around
> this with rules on update and delete on the storagerecords table but
> these may be causing more problems than they solve by launching a
> cascade of requests every time this table is touched (locking the whole
> table, long sequential scans, etc.).
> 
> We tried to change the storagerecords.value column type to "oid" (alter
> table storagerecords alter column value type oid using
> storagerecords.value::oid) and it seems to work better, at least
> vacuumlo doesn't go on a deleting spree any more and is now usable to
> purge truly orphaned objects.
> 
> What do you think?
> 
>  Etienne
> 
> -- 
> To unsubscribe from this list send an email to users-unsubscribe at shibboleth.net