Lists: | pgsql-general |
---|
From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | lo_copy() |
Date: | 2003-04-01 07:29:56 |
Message-ID: | Pine.LNX.4.21.0304010738390.2573-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: lo_copy() |
Date: | 2003-04-01 15:15:27 |
Message-ID: | 782.1049210127@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)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
From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: lo_copy() |
Date: | 2003-04-01 15:26:27 |
Message-ID: | Pine.LNX.4.21.0304011620440.2573-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Tue, 1 Apr 2003, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)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