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

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

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.

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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.

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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.

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

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.

postgresql backup restore blob

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

edited Dec 7 '18 at 5:59

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

asked Dec 5 '18 at 15:48

Bằng Rikimaru

1184

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

bumped to the homepage by Community♦ 2 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

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

add a comment |

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

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

add a comment |

1 Answer
1

active

oldest

votes

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.

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224220%2fpostgresql-large-object-exists-in-pg-largeobject-table-but-cannot-open-with-lo%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Large objects were restored from a dump with this command:

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

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?

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

add a comment |

Large objects were restored from a dump with this command:

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

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?

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

add a comment |

Large objects were restored from a dump with this command:

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

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?

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

Large objects were restored from a dump with this command:

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

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?

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

answered Dec 7 '18 at 11:13

Daniel Vérité

17.3k33551

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Database Administrators Stack Exchange!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Xrhrft