Postgres Pro
Downloads
Home   >   mailing lists

Thread: lo_copy()

lo_copy()

From
"Nigel J. Andrews"
Date:

I wonder if anyone has any comments on this [psuedo] code:

new loid := SELECT lo_creat(131072 + 262144);

UPDATE pg_largeobject SET
    data = (SELECT data
            FROM pg_largeobject
            WHERE loid = <source loid> AND pageno = 0)
    WHERE loid = <new loid> AND pageno = 0;

INSERT INTO pg_largeobject
    (loid, pageno, data)
    (SELECT <new loid>, pageno, data
        FROM pg_largeobject
        WHERE loid = <source loid> AND pageno > 0
    );


It does seem to work but I wouldn't like to swear it's safe, hence this
posting.

I was originally looking for a newoid() function to use, then thought about
wrapping my own seeing as there wasn't one but then spotted there were some lo_
functions available. Therefore, lo_creat() I'm really just using to grab a new
oid for the new large object.


--
Nigel J. Andrews

Re: lo_copy()

From
Tom Lane
Date:
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> I wonder if anyone has any comments on this [psuedo] code:

> new loid := SELECT lo_creat(131072 + 262144);

> UPDATE pg_largeobject SET
>     data = (SELECT data
>             FROM pg_largeobject
>             WHERE loid = <source loid> AND pageno = 0)
>     WHERE loid = <new loid> AND pageno = 0;

> INSERT INTO pg_largeobject
>     (loid, pageno, data)
>     (SELECT <new loid>, pageno, data
>         FROM pg_largeobject
>         WHERE loid = <source loid> AND pageno > 0
>     );

I believe this will work, but it requires superuser privileges to
scribble on pg_largeobject directly.  Probably would be better to go
through the gruntwork of creating a fully supported lo_copy() operation.

            regards, tom lane

Re: lo_copy()

From
"Nigel J. Andrews"
Date:
On Tue, 1 Apr 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > I wonder if anyone has any comments on this [psuedo] code:
>
> > new loid := SELECT lo_creat(131072 + 262144);
>
> > UPDATE pg_largeobject SET
> >     data = (SELECT data
> >             FROM pg_largeobject
> >             WHERE loid = <source loid> AND pageno = 0)
> >     WHERE loid = <new loid> AND pageno = 0;
>
> > INSERT INTO pg_largeobject
> >     (loid, pageno, data)
> >     (SELECT <new loid>, pageno, data
> >         FROM pg_largeobject
> >         WHERE loid = <source loid> AND pageno > 0
> >     );
>
> I believe this will work, but it requires superuser privileges to
> scribble on pg_largeobject directly.  Probably would be better to go
> through the gruntwork of creating a fully supported lo_copy() operation.
>
>             regards, tom lane
>

Thanks Tom, I was just a little worried that the page 0 record contained some
housekeeping type data that really shouldn't be copied from another page
0.

I noticed the scribbling on pg_largeobject priviledge and had created the
function with '...INVOKE AS DEFINER...' with a switch into superuser just for
that function's definition. Apart from making it more suitable for inclusion in
the main distribution, and possibly speed, I'm not sure what recoding in C
gives me given that I'm happy at the moment to do it this way. I may look into
a C version for submision later.


--
Nigel J. Andrews