Обсуждение: Removing large objects from the db (Q at bottom)

Let's say you've got the following relation in a database:

CREATE TABLE foo (
    descr    VARCHAR(32),
    content    OID
);

and you do a

INSERT INTO foo (descr, content)
    VALUES ('obj1', lo_import('/tmp/bar.txt'));

then you'll have one tuple in your relation with the oid of the imported
'bar.txt' in the content column, so if you were to do a

SELECT * FROM foo;

you might get something that looks like

descr|content
-----+-------
obj1 | 166219
(1 row)

Now, if you do a

DELETE FROM foo WHERE descr = 'obj1';

you'll wind up deleting the tuple in foo, but if you do a

SELECT lo_export(166219, '/tmp/frotz.txt');

you'll find that the large object is still in the database, which makes
sense, since you're only deleting a pointer to the lo. I apologise for the
pedantry -- I just wanted to make sure that I was on solid ground before I
asked what will very probably be a silly question: How do you delete a
large object from the database? I didn't see a built in function, and there
wasn't anything in section 15 of the programmer's manual.

--
Alexander Garrett - Information Architect, Demiurge Electronic Publishing
  * like tiny insects in the palm of history a domino effect in a cloud
  * of mystery  my writing is  an iron fist in a glove full of vaseline
  * but i dip the fuse in the kerosene i too become a dissident - Dolby
agarrett@demiurge.org * Save the Adverb! * http://www.demiurge.org/adverb
--
Note: This message was sent by a robot.

On Wed, 30-Dec-98, jagged@shaitan.demiurge.org
asked about how to delete Large Objects.

> Now, if you do a

> DELETE FROM foo WHERE descr = 'obj1';

> you'll wind up deleting the tuple in foo, but if you do a

> SELECT lo_export(166219, '/tmp/frotz.txt');

> you'll find that the large object is still in the database, which makes
> sense, since you're only deleting a pointer to the lo. I apologise for the
> pedantry -- I just wanted to make sure that I was on solid ground before I
> asked what will very probably be a silly question: How do you delete a
> large object from the database? I didn't see a built in function, and
there
> wasn't anything in section 15 of the programmer's manual.

I too ran across this problem. I was surprised, given that the design of the
interface was to look like UNIX file operations that there wasn't some kind
of an "unlink" command. I went to the source code and found that indeed
there is exactly that command.  I know that Bruce Mojian spent some time
last year making sure that the large objects worked, so I expected that with
release 6.4 the documentation would be updated as well. It doesn't appear to
have been, however, so I'll make the specific request that the documentation
for Large Objects be "reviewed". It looked to me, from the source code, that
everything was in place - its just that it isn't fully documented.

The output from "ctags -x" on .../src/interfaces/libpq/fe-lobj.c shows the
following functions are available:

lo_close          93 fe-lobj.c        lo_close(
lo_creat         267 fe-lobj.c        lo_creat(
lo_export        448 fe-lobj.c        lo_export(
lo_import        383 fe-lobj.c        lo_import(
lo_initialize    509 fe-lobj.c        lo_initialize(
lo_lseek         218 fe-lobj.c        lo_lseek(
lo_open           46 fe-lobj.c        lo_open(
lo_read          132 fe-lobj.c        lo_read(
lo_tell          305 fe-lobj.c        lo_tell(
lo_unlink        343 fe-lobj.c        lo_unlink(
lo_write         172 fe-lobj.c        lo_write(

In many cases one can guess what the return values will be, however, in a
few cases, the meaning of error-returns is not clear.

I am willing to help work on upgrading some documentation here, but I am
not terribly knowlegeable about the ins and outs of these routines.  If
someone who is will work with me (say, I write and the other person reviews
and corrects?) we may be able to update some of this...

Mark Dalphin                                   mdalphin@amgen.com

PS Sorry about any "funny" formating here; this is a brand new mailer to
me and I am not at all comfortable with it!