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.
postgresql backup restore blob
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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.
postgresql backup restore blob
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
bumped to the homepage by Community♦ 1 min 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-042704 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 ofselect * 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 forCOPY 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.
postgresql backup restore blob
asked Dec 5 '18 at 15:48
Bằng RikimaruBằ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
postgresql backup restore blob
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
edited Dec 7 '18 at 5:59
Bằng Rikimaru
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
asked Dec 5 '18 at 15:48
Bằng RikimaruBằng Rikimaru
1184
1184
bumped to the homepage by Community♦ 1 min 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♦ 1 min 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-042704 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 ofselect * 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 forCOPY 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-042704 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 ofselect * 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 forCOPY 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
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
@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
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
@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é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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
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é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;
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é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;
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éDaniel Vérité
17.3k33551
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éDaniel Vérité
17.3k33551
answered Dec 7 '18 at 11:13
Daniel VéritéDaniel Vérité
17.3k33551
answered Dec 7 '18 at 11:13
Daniel VéritéDaniel Vérité
17.3k33551
answered Dec 7 '18 at 11:13
Daniel VéritéDaniel Vérité
17.3k33551
17.3k33551
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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