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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general