Thread: pg_largeobject related issue with 9.2

pg_largeobject related issue with 9.2

From
sramay
Date:
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.


Re: pg_largeobject related issue with 9.2

From
Kevin Grittner
Date:
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


Re: pg_largeobject related issue with 9.2

From
sramay
Date:
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.


Re: pg_largeobject related issue with 9.2

From
Kevin Grittner
Date:
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


Re: pg_largeobject related issue with 9.2

From
sramay
Date:
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.


Re: pg_largeobject related issue with 9.2

From
sramay
Date:
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.