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












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.










share|improve this question
















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-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


















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.










share|improve this question
















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-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
















0












0








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.










share|improve this 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.







postgresql backup restore blob






share|improve this question















share|improve this question













share|improve this question




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




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-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



















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












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























    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


















    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









    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




























      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


























        0












        0








        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













        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












        share|improve this answer



        share|improve this answer










        answered Dec 7 '18 at 11:13









        Daniel VéritéDaniel Vérité

        17.3k33551




        17.3k33551






























            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














            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





















































            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







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh