0

I have a problem with opening Large Object in postgresql version 9.2,

select * from pg_largeobject where loid = '19423';

will return data like this:

loid oid;page no integer;data bytea
19423;0;"<Comment>ripple height</Comment><Units>meter</Units><Model>TRIM2</Model><Title>Results of TRIM model (Hartmut Kapitza): bottom temperature, bottom salinity, bed shear stress generated by currents. Results of WAM model (Heinz Guenther, Ralf Weisse): bed she (...)"

However, when I try to open with

select lo_open(19423, x'40000'::int);

It returns error:

ERROR: large object 19423 does not exist
SQL state: 42704

The result of this query:

select * from pg_largeobject_metadata where oid = 19423

is empty: lomowner oid; lomacl aclitem[]

What is this kind of error in Postgresql? I have no clue about it. The data was restored from 1 postgresql dump .sql file.

  • stackoverflow.com/questions/48840921/libpq-lo-open-returns-0 42704 undefined_object – McNets Dec 5 '18 at 16:19
  • @McNets it is not problem as same as mine. – Bằng Rikimaru Dec 6 '18 at 8:32
  • Please include the result of select * from pg_largeobject_metadata where oid = 19423 – Daniel Vérité Dec 6 '18 at 19:51
  • @DanielVérité I tried the query you suggested but it returns empty (I updated in the description). So you think that pg_largeobject is not enough, it also needs to exist in pg_largeobject_metadata table? (the restore dump file I got only contains commands for COPY pg_largeobject (loid, pageno, data) FROM stdin; – Bằng Rikimaru Dec 7 '18 at 5:59
0

Large objects were restored from a dump with this command:

COPY pg_largeobject (loid, pageno, data) FROM stdin;

The problem is that it's not sufficient, because since PostgreSQL 9.0, which added access privileges to large objects, they are stored into two tables: pg_largeobject_metadata with one row per object and a unique index on oid, and pg_largeobject, which one row per page of data per object.

So the above way of dumping large objects is obsolete, now it looks like, for each large object:

BEGIN;
SELECT pg_catalog.lo_open('16401', 131072);
SELECT pg_catalog.lowrite(0, '\x23207e2f2e6261736872633a2....')
SELECT pg_catalog.lo_close(0);
COMMIT;

How to fix the problem of the missing entries in pg_largeobject_metadata?

Because oid is a system column, I don't think you can just insert into it to manually to create the missing entries. A clean way to deal with the problem would be to generate a script looking like the above based on the current contents of pg_largeobject, then truncate pg_largeobject, then play the script to reimport the data the right way.

| improve this answer | |
0

When you backup the database have the "use Oids" Checked. When you restore the database again it will have the object Ids (I suppose ) This is what i did and it was fixed , i backup it up with the option "Use Oids" and when restored all worked fine . :)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.