IDP V3: LockTimeoutException with remote PG DB

[Etienne Dysli-Metref]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 836 bytes
Desc: OpenPGP digital signature
URL: <http://shibboleth.net/pipermail/users/attachments/20151020/62bdd4f2/attachment.sig>