Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?

Question

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.

• Bằng Rikimaru: @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;

Answer 1

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.

Answer 2

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 . :)