Ygthkb

How vim overwrites readonly mode?

Does the ditching switch allow an A320 to float indefinitely?

Am I correct in stating that the study of topology is purely theoretical?

Potential client has a problematic employee I can't work with

Translation needed for 130 years old church document

Why do we have to make "peinlich" start with a capital letter and also end with -s in this sentence?

What can I do to encourage my players to use their consumables?

Why is the "Domain users" group missing from this Powershell AD Query?

Is there a verb that means to inject with poison?

Calculate of total length of edges in Voronoi diagram

Do authors have to be politically correct in article-writing?

Why are carbons of Inositol chiral centers?

Taking headphones when quitting job

Subsurf on a crown. How can I smooth some edges and keep others sharp?

Does Skippy chunky peanut butter contain trans fat?

Renting a 2CV in France

Plausible reason for gold-digging ant

Why does 0.-5 evaluate to -5?

Is Screenshot Time-tracking Common?

Which scales have a major chord built on second note? Which scales have a minor chord built on the seventh note?

What senses are available to a corpse subjected to a Speak with Dead spell?

Stuck on a Geometry Puzzle

Coworker asking me to not bring cakes due to self control issue. What should I do?

Does the US government have any planning in place to ensure there's no shortages of food, fuel, steel and other commodities?

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

PostgreSQL is running locally but I cannot connect. Why?Is PostgreSQL appropriate for processing this large but simple dataset?PostgreSQL - partial dump of large objects (pg_largeobject table)Postgres pg_basebackup reports directory exists but is not emptyPostgreSQL: options for large tableCannot open backup device ErrorReplacing a large object in PostgreSQLpostgresql: PANIC: cannot operate with inconsistent datapostgresql Cannot login with created usersPostgreSQL - Index Not Used on Large Table

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.

postgresql backup restore blob

share|improve this question

edited Dec 7 '18 at 5:59

Bằng Rikimaru

asked Dec 5 '18 at 15:48

Bằng RikimaruBằng Rikimaru

1184

bumped to the homepage by Community♦ 6 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 | 

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.

postgresql backup restore blob

share|improve this question

edited Dec 7 '18 at 5:59

Bằng Rikimaru

asked Dec 5 '18 at 15:48

Bằng RikimaruBằng Rikimaru

1184

bumped to the homepage by Community♦ 6 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.

postgresql backup restore blob

share|improve this question

edited Dec 7 '18 at 5:59

Bằng Rikimaru

asked Dec 5 '18 at 15:48

Bằng RikimaruBằng Rikimaru

1184

select * from pg_largeobject where loid = '19423';

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 (...)"

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

ERROR: large object 19423 does not exist

SQL state: 42704

select * from pg_largeobject_metadata where oid = 19423

share|improve this question

edited Dec 7 '18 at 5:59

Bằng Rikimaru

asked Dec 5 '18 at 15:48

Bằng RikimaruBằng Rikimaru

1184

share|improve this question

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

1 Answer
1

active

oldest

votes

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.

share|improve this answer

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
});


}
});

draft saved

draft discarded

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

Name

Email

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

Email

Required, but never shown

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.

share|improve this answer

answered Dec 7 '18 at 11:13

Daniel VéritéDaniel Vérité

17.3k33551

add a comment | 

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.

share|improve this answer

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.

share|improve this answer

answered Dec 7 '18 at 11:13

Daniel VéritéDaniel Vérité

17.3k33551

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

BEGIN;

SELECT pg_catalog.lo_open('16401', 131072);

SELECT pg_catalog.lowrite(0, 'x23207e2f2e6261736872633a2....')

SELECT pg_catalog.lo_close(0);

COMMIT;

share|improve this answer

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