Thread: pg_largeobject related issue with 9.2
Hi,
I am having a application which was running on Jboss 5 with Hibernate and
PostgreSQL 9.2. Due to media corruption. Data without largeobject was
restored and largeobject I restored from some other source.
Now the application is giving error eventhough largeobject is present it is
giving error. Can any help me?
--- part of log ---
Caused by: org.hibernate.exception.SQLGrammarException: could not execute
query
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
at org.hibernate.loader.Loader.list(Loader.java:2023)
at
org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at
org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at
org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at nic.scbpds.db.DataBaseUtil.getUnionList(Unknown Source)
at
nic.scbpds.allocation.business.CentralAllocationImpl.getUnionList(Unknown
Source)
at
nic.scbpds.allocation.form.common.controller.AllocationController.getUnionList(Unknown
Source)
at nic.scbpds.allocation.form.CentralDashboardWebPage.<init>(Unknown
Source)
at nic.scbpds.allocation.form.CentralDashboardWebPage.<init>(Unknown
Source)
... 39 more
*Caused by: org.postgresql.util.PSQLException: ERROR: large object 141066
does not exist*
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
at
org.postgresql.core.v3.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:650)
at
org.postgresql.core.v3.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:480)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:72)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:112)
at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:124)
at
org.postgresql.largeobject.LargeObject.<init>(LargeObject.java:91)
at
org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:200)
at
org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:170)
at
org.postgresql.jdbc2.AbstractJdbc2BlobClob.<init>(AbstractJdbc2BlobClob.java:45)
at
org.postgresql.jdbc2.AbstractJdbc2Blob.<init>(AbstractJdbc2Blob.java:19)
at
org.postgresql.jdbc3.AbstractJdbc3Blob.<init>(AbstractJdbc3Blob.java:17)
at
org.postgresql.jdbc4.AbstractJdbc4Blob.<init>(AbstractJdbc4Blob.java:18)
at org.postgresql.jdbc4.Jdbc4Blob.<init>(Jdbc4Blob.java:18)
at
org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(Jdbc4ResultSet.java:49)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:344)
at
org.jboss.resource.adapter.jdbc.WrappedResultSet.getBlob(WrappedResultSet.java:386)
at org.hibernate.type.BlobType.get(BlobType.java:57)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:111)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at
org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2031)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)
at
org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1299)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:568)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 50 more
Thanks in advance
Rama
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-largeobject-related-issue-with-9-2-tp5784969.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
sramay <nic.srama@gmail.com> wrote: > I am having a application which was running on Jboss 5 with > Hibernate and PostgreSQL 9.2. Due to media corruption. Data > without largeobject was restored and largeobject I restored from > some other source. > > Now the application is giving error eventhough largeobject is > present it is giving error. Can any help me? > ERROR: large object 141066 does not exist* It appears that you restored the large objects from a different point in the series of commits than the rest of the database, and you therefore have object IDs for large objects that don't exist. Either you need to modify your software to deal with that situation more gracefully, or you need to identify where you have a mismatch and fix your data. You might try a set of queries something like: SELECT * FROM tabname t WHERE NOT EXISTS (SELECT * FROM pg_largeobject o WHERE o.loid = t.colname); -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Kevin, I will use whatever techniques you have mentioned. The situation is unique there was no backup for 300+ GB Database. If I give the command select * from pg_largeobject where loid=141066; it is showing 3 rows But whenever I want to export to lo_export it says loid missing it says. If u can give us some more hints of exporting whatever is visible it will be of great help. Thanking u in advance Rama -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-largeobject-related-issue-with-9-2-tp5784969p5786257.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
sramay <nic.srama@gmail.com> wrote: > select * from pg_largeobject where loid=141066; > > it is showing 3 rows > > But whenever I want to export to lo_export it says loid missing Perhaps pageno = 0 is missing for that object? Perhaps you need something in pg_largeobject_metadata for the object permissions? I would compare the entries for the large objects which are not working with the entries that are working. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Kevin, Thanks for the prompt answer. The Page 0 of the record is very much existing. But still If take a dump of pg_largeobject_metadata from source database still the same message. The database runs under the 'postgres' superuser only. Can I recreate the pg_largeobject_metadata by which command? Thanks in advance . Regards Rama -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-largeobject-related-issue-with-9-2-tp5784969p5786648.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi Kevin,
From Mr Momijan's suggestion by running the following query, I was in a
position
to create the pg_largeobject_metadata table instead of copying, I assumed
that
postgres is the user at source and hence it should work for destination
also. But
by running the following command
select pg_catalog.lo_create(t.loid)
from (sleect distinct loid from pg_catalog.pg_largeobject) as t;
My issues are solved. Thanks for the hint to you and to Mr. Momijan.
Regards
Rama
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-largeobject-related-issue-with-9-2-tp5784969p5786658.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.