Hi all,

I am trying to copy blobs between two tables and need help on the best way
to get this done. My requirement is that the both the tables maintain their
own copy of the large object, instead of sharing the OID.

I created two tables:

create table table_a
(id bigserial not null,
filename oid);

create table table_b
(id bigserial not null,
filename oid);

In one of the tables, I uploaded a file from the filesystem.

INSERT INTO table_a (id, filename)
VALUES ( nextval('table_a_id_seq'),
lo_import('C:/applications/largeobj.zip'));

The record is inserted, and I verified the record's integrity by:

SELECT lo_export(filename, 'C:/applications/largeobj.zip_copy.zip') FROM
table_a;

Question
----------------
Now to make a copy of the object from table to table_a to table_a. Currently
I am exporting the file from table_a to the file system, and again doing an
import into table_b. However, in a large application, I find this
workaround not practical because of the volume of the records, and also the
size of the file (binary object). My ideal solution to do an insert of the
values from table_a into table_b directly.

So, what is best way to create a copy of this LOB from table_a to table_b?

Appreciate any responses.

Thank you.

John

Search Discussions

  • Diogo Biazus at Oct 17, 2008 at 8:32 pm

    Em 17/10/2008, às 18:18, John Skillings escreveu:

    Hi all,

    I am trying to copy blobs between two tables and need help on the
    best way to get this done. My requirement is that the both the
    tables maintain their own copy of the large object, instead of
    sharing the OID.

    I created two tables:

    create table table_a
    (id bigserial not null,
    filename oid);

    create table table_b
    (id bigserial not null,
    filename oid);

    In one of the tables, I uploaded a file from the filesystem.

    INSERT INTO table_a (id, filename)
    VALUES ( nextval('table_a_id_seq'), lo_import('C:/
    applications/largeobj.zip'));

    The record is inserted, and I verified the record's integrity by:

    SELECT lo_export(filename, 'C:/applications/largeobj.zip_copy.zip')
    FROM table_a;

    Question
    ----------------
    Now to make a copy of the object from table to table_a to table_a.
    Currently I am exporting the file from table_a to the file system,
    and again doing an import into table_b. However, in a large
    application, I find this workaround not practical because of the
    volume of the records, and also the size of the file (binary
    object). My ideal solution to do an insert of the values from
    table_a into table_b directly.

    So, what is best way to create a copy of this LOB from table_a to
    table_b?
    You can copy only the oid, You don't need to have another copy of the
    same file in the database, if you copy only the oid you'll have
    another reference to the same file.

    So a simple
    INSERT INTO table_a SELECT * FROM table_b;
    will do the trick in your example.
  • John Skillings at Oct 17, 2008 at 8:37 pm
    Hi Diogo,

    Thanks for your response. However, my requirement is to have two separate
    copies. Think about table_a being the original copy, and table_b holding
    the backup copy.

    Inserting the OID from table_a to table_b will not make the backup copy.

    Best Regards,

    John

    On Fri, Oct 17, 2008 at 12:32 PM, Diogo Biazus wrote:


    Em 17/10/2008, às 18:18, John Skillings escreveu:


    Hi all,
    I am trying to copy blobs between two tables and need help on the best way
    to get this done. My requirement is that the both the tables maintain their
    own copy of the large object, instead of sharing the OID.

    I created two tables:

    create table table_a
    (id bigserial not null,
    filename oid);

    create table table_b
    (id bigserial not null,
    filename oid);

    In one of the tables, I uploaded a file from the filesystem.

    INSERT INTO table_a (id, filename)
    VALUES ( nextval('table_a_id_seq'),
    lo_import('C:/applications/largeobj.zip'));

    The record is inserted, and I verified the record's integrity by:

    SELECT lo_export(filename, 'C:/applications/largeobj.zip_copy.zip') FROM
    table_a;

    Question
    ----------------
    Now to make a copy of the object from table to table_a to table_a.
    Currently I am exporting the file from table_a to the file system, and again
    doing an import into table_b. However, in a large application, I find this
    workaround not practical because of the volume of the records, and also the
    size of the file (binary object). My ideal solution to do an insert of the
    values from table_a into table_b directly.

    So, what is best way to create a copy of this LOB from table_a to
    table_b?
    You can copy only the oid, You don't need to have another copy of the same
    file in the database, if you copy only the oid you'll have another reference
    to the same file.

    So a simple
    INSERT INTO table_a SELECT * FROM table_b;
    will do the trick in your example.

    --
    Diogo Biazus
    diogob@gmail.com
    http://www.softa.com.br
    http://www.postgresql.org.br
  • Csaba Nagy at Oct 20, 2008 at 11:33 am

    On Fri, 2008-10-17 at 13:40 -0700, John Skillings wrote:
    Hello Csaba,

    Back in the month of April, I noticed that you posted a similar
    request on copying blobs between two tables, having separate OID.

    Can you let me know your final solution please.
    The final version I'm using is this one:

    CREATE OR REPLACE FUNCTION copy_blob(p_blobId oid)
    RETURNS oid
    AS '
    DECLARE
    v_NewOID OID;
    v_LODesc INTEGER;
    BEGIN
    SELECT lo_create(0) INTO v_NewOID;

    SELECT lo_open(v_NewOID, -1) INTO v_LODesc;

    PERFORM lowrite(v_LODesc, data) FROM pg_largeobject WHERE loid =
    p_blobId ORDER BY pageno;

    PERFORM lo_close(v_LODesc);

    RETURN v_NewOID;
    END;
    ' LANGUAGE 'plpgsql';

    The only drawback is that you must enable reading of pg_largeobject for
    the application user, as by default only the postgres super user can
    access it. You would do that with something like (as postgres super
    user):

    GRANT SELECT ON pg_largeobject TO my_app_user;

    Cheers,
    Csaba.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 17, '08 at 8:19p
activeOct 20, '08 at 11:33a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase