From MGoldner@agmednet.com Wed Oct 3 16:04:56 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C818A9F9654 for ; Wed, 3 Oct 2007 13:04:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 38039-02 for ; Wed, 3 Oct 2007 13:04:53 -0300 (ADT) X-Greylist: delayed 00:12:14.261374 by SQLgrey-1.7.5 Received: from boston.agmednet.com (boston.agmednet.com [74.92.47.82]) by postgresql.org (Postfix) with ESMTP id BF3749F98AA for ; Wed, 3 Oct 2007 13:04:53 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Received: from 74.92.47.81 ([74.92.47.81]) by agmednet-dc.boston.agmednet.local ([192.168.67.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 3 Oct 2007 15:50:53 +0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C805D5.2D8D538E" Received: from mgoldner-desktop by boston.agmednet.com; 03 Oct 2007 11:52:36 -0400 Organization: AG Mednet, Inc. X-Mailer: Evolution 2.8.1 Content-class: urn:content-classes:message Subject: Testing lo_unlink() in psql Date: Wed, 3 Oct 2007 11:52:36 -0400 Message-ID: <1191426756.10212.20.camel@mgoldner-desktop> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Testing lo_unlink() in psql thread-index: AcgF1S2NOxRD4ATdRbyyFSTtw5iJkg== From: "Michael Goldner" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.124 tagged_above=0 required=5 tests=AWL=0.241, BAYES_40=-0.185, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=2.067 X-Spam-Level: ** X-Archive-Number: 200710/13 X-Sequence-Number: 26927 This is a multi-part message in MIME format. ------_=_NextPart_001_01C805D5.2D8D538E Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running postgres 8.1.8 on redhat. I have a very large table that contains an OID that refers to a large object. I have a rule defined that calls lo_unlink() on a delete. I seem to get a successful return status on the delete, but my total disk space used does not seem to shrink. I've run "VACUUM ANALYZE" on the table after the delete and have the same behavior. Do I need to run a "VACUUM FULL" to get the space back after an lo_unlink()? Is there some other reason I'm not reclaiming disk space after an lo_unlink()? Also, is there any way to determine the actual filename that the OID references? I'd like to be able to do an "ls -l " before and after the lo_unlink() command to confirm that the file has been removed. I can't really do lo_list from psql because the table is > 2.5 million rows. =20 Thanks, Mike ------_=_NextPart_001_01C805D5.2D8D538E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Testing lo_unlink() in psql

I'm running postgres 8.1.8 on redhat.

I have a very large table that contains an OID that refers to a = large
object.  I have a rule defined that calls lo_unlink() on a = delete.  I
seem to get a successful return status on the delete, but my total = disk
space used does not seem to shrink.

I've run "VACUUM ANALYZE" on the table after the delete and = have the
same behavior.  Do I need to run a "VACUUM FULL" to get = the space back
after an lo_unlink()?

Is there some other reason I'm not reclaiming disk space after an
lo_unlink()?

Also, is there any way to determine the actual filename that the OID
references?  I'd like to be able to do an "ls -l = <filename>" before and
after the lo_unlink() command to confirm that the file has been = removed.

I can't really do lo_list from psql because the table is > 2.5 = million
rows. 

Thanks,

Mike

------_=_NextPart_001_01C805D5.2D8D538E-- From tgl@sss.pgh.pa.us Wed Oct 3 16:25:14 2007 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 792AA9F9C55 for ; Wed, 3 Oct 2007 13:25:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82798-05 for ; Wed, 3 Oct 2007 13:25:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 526FB9F9A55 for ; Wed, 3 Oct 2007 13:25:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l93GP6BR025836; Wed, 3 Oct 2007 12:25:06 -0400 (EDT) To: "Michael Goldner" cc: pgsql-admin@postgresql.org Subject: Re: Testing lo_unlink() in psql In-reply-to: <1191426756.10212.20.camel@mgoldner-desktop> References: <1191426756.10212.20.camel@mgoldner-desktop> Comments: In-reply-to "Michael Goldner" message dated "Wed, 03 Oct 2007 11:52:36 -0400" Date: Wed, 03 Oct 2007 12:25:06 -0400 Message-ID: <25835.1191428706@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/14 X-Sequence-Number: 26928 "Michael Goldner" writes: > I've run "VACUUM ANALYZE" on the table after the delete and have the > same behavior. Do I need to run a "VACUUM FULL" to get the space back > after an lo_unlink()? Vacuuming the table isn't gonna help much, because all that reclaims is your table row, not the space occupied by the large object. Large objects live in pg_largeobject, and you need to be vacuuming *that* on a regular basis if you do much deleting or updating of large objects. > Also, is there any way to determine the actual filename that the OID > references? You appear to be seriously confused about how large objects are stored. See http://www.postgresql.org/docs/8.1/static/largeobjects.html http://www.postgresql.org/docs/8.1/static/catalog-pg-largeobject.html regards, tom lane