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

backupblobpostgresqlrestore

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.

Best Answer

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.

Related Question