From Premsun@nsasia.co.th Fri Oct  7 04:28:03 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 779BFD981D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 01:28:03 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 22314-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 04:28:00 +0000 (GMT)
Received: from mail.nsasia.co.th (mail.nsasia.co.th [203.146.6.34])
	by svr1.postgresql.org (Postfix) with ESMTP id 8AE15DA471
	for <pgsql-sql@postgresql.org>; Fri,  7 Oct 2005 01:27:58 -0300 (ADT)
Received: from [192.168.200.39] (helo=mail.nsasia.co.th)
	by astaro.nsasia.co.th with esmtp (Exim 4.43) id 1ENjpr-0003dA-CD
	for pgsql-sql@postgresql.org; Fri, 07 Oct 2005 11:27:55 +0700
Received: from Thailand-MTA by mail.nsasia.co.th
	with Novell_GroupWise; Fri, 07 Oct 2005 11:27:45 +0700
Message-Id: <43465BAA.C5F7.004C.0@nsasia.co.th>
X-Mailer: Novell GroupWise Internet Agent 7.0 
Date: Fri, 07 Oct 2005 11:27:40 +0700
From: "Premsun Choltanwanich" <Premsun@nsasia.co.th>
To: <pgsql-sql@postgresql.org>
Subject: How to delete Large Object from Database?
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__PartBB99B02C.0__="
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.078 required=5 tests=[AWL=0.021, HTML_30_40=0.056,
	HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200510/69
X-Sequence-Number: 22922

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__PartBB99B02C.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Dear All,
=20
I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine for =
me to get and put Large Object  from and to database. However I found =
something that may not correct when I try to backup my data. It seem that =
I cannot delete Large Object from database. It seem the thing I can do is =
only delete the reference oid from table but Object still in database.
=20
A Detail shown below is  reason I think that I cannot delete Large Object =
out of database.
=20
14MB of file size when BackUp Data (BLOB Included)
900KB of file size when BackUp Data (BLOB Excluded)
13MB of file size when BackUp Data after delete all data from table that =
has 'lo' column. (BLOB Included)=20
=20
So,How to delete Large Object from Database? or Is my understanding wrong?


--=__PartBB99B02C.0__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dwindows-874=
">
<META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Microsoft Sans Serif">
<DIV>Dear All,</DIV>
<DIV>&nbsp;</DIV>
<DIV>I use '$libdir/lo' for manage my PostgreSQL Large Object. It work =
fine for me to get and put Large Object&nbsp; from and to database. =
However I found something that may not correct when I try to backup my =
data. It seem that I cannot delete Large Object from database. It seem the =
thing I can do is only delete the reference oid from table but Object =
still in database.</DIV>
<DIV>&nbsp;</DIV>
<DIV>A Detail shown below is&nbsp; reason I think that I cannot delete =
Large Object out of database.</DIV>
<DIV>&nbsp;</DIV>
<DIV>14MB of file size when BackUp Data (BLOB Included)</DIV>
<DIV>
<DIV>900KB of file size when BackUp Data (BLOB Excluded)</DIV>
<DIV>
<DIV>13MB of file size when BackUp Data after delete all data from table =
that has&nbsp;'lo' column.&nbsp;(BLOB Included)&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>So,How to delete Large Object from Database? or Is my understanding =
wrong?</DIV></DIV></DIV></BODY></HTML>

--=__PartBB99B02C.0__=--



From dev@archonet.com Fri Oct  7 09:09:05 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 11817DA525
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 06:09:05 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78553-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Oct 2005 09:08:56 +0000 (GMT)
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by svr1.postgresql.org (Postfix) with ESMTP id DF346DA48E
	for <pgsql-sql@postgresql.org>; Fri,  7 Oct 2005 04:30:29 -0300 (ADT)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id 4A9C240C48A; Fri,  7 Oct 2005 08:30:25 +0100 (BST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP id 6510215EF9;
	Fri,  7 Oct 2005 08:30:16 +0100 (BST)
Received: from mainbox.archonet.com ([127.0.0.1])
	by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 09135-09; Fri, 7 Oct 2005 08:30:12 +0100 (BST)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id E808815EDB;
	Fri,  7 Oct 2005 08:30:05 +0100 (BST)
Message-ID: <434623FD.7000408@archonet.com>
Date: Fri, 07 Oct 2005 08:30:05 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050912)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Premsun Choltanwanich <Premsun@nsasia.co.th>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to delete Large Object from Database?
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
In-Reply-To: <43465BAA.C5F7.004C.0@nsasia.co.th>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.058 required=5 tests=[AWL=0.008,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200510/71
X-Sequence-Number: 22924

Premsun Choltanwanich wrote:
> 
> Dear All,
>  
> I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine 
> for me to get and put Large Object  from and to database. However I 
> found something that may not correct when I try to backup my data. It 
> seem that I cannot delete Large Object from database. It seem the thing 
> I can do is only delete the reference oid from table but Object still in 
> database.

> So,How to delete Large Object from Database? or Is my understanding wrong?

Deleting the OID does not remove the object itself - see details of 
lo_unlink() in Chapter 28 of the manuals.

I seem to recall some other utilities in the contrib/ directory of the 
source distribution too.

-- 
   Richard Huxton
   Archonet Ltd



From volkan.yazici@gmail.com Sat Oct  8 11:20:06 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EC9B1DA65A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  8 Oct 2005 08:20:06 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 12408-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  8 Oct 2005 11:20:01 +0000 (GMT)
Received: from qproxy.gmail.com (qproxy.gmail.com [72.14.204.206])
	by svr1.postgresql.org (Postfix) with ESMTP id 7F655DA644
	for <pgsql-sql@postgresql.org>; Sat,  8 Oct 2005 08:20:02 -0300 (ADT)
Received: by qproxy.gmail.com with SMTP id e12so789971qbe
	for <pgsql-sql@postgresql.org>; Sat, 08 Oct 2005 04:20:05 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=lLnhPfp9aRkgzYr2AvX80lA20t1waOEQYxHgB6eCNV/vq0gENkAQNZKAU7LS7ZNTykp+URhLJkPDvWr+9ajQTh9nQQlSxU84Ssv5245OqlAYRZ5nPw4Q6mOAbnzi597jmQ6p35/6juAc4Y7n1EjCPr5TXyEN4RqbJdCzP0dbDG8=
Received: by 10.65.154.13 with SMTP id g13mr2030614qbo;
	Sat, 08 Oct 2005 04:20:05 -0700 (PDT)
Received: by 10.65.115.7 with HTTP; Sat, 8 Oct 2005 04:20:05 -0700 (PDT)
Message-ID: <7104a7370510080420qca482a5r23ce3fdff80d25e1@mail.gmail.com>
Date: Sat, 8 Oct 2005 14:20:05 +0300
From: Volkan YAZICI <volkan.yazici@gmail.com>
To: Richard Huxton <dev@archonet.com>
Subject: Re: How to delete Large Object from Database?
Cc: Premsun Choltanwanich <Premsun@nsasia.co.th>,
	pgsql-sql@postgresql.org
In-Reply-To: <434623FD.7000408@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.856 required=5 tests=[RCVD_BY_IP=0.024,
	RCVD_IN_BL_SPAMCOP_NET=1.832]
X-Spam-Level: *
X-Archive-Number: 200510/74
X-Sequence-Number: 22927

Hi,

On 10/7/05, Richard Huxton <dev@archonet.com> wrote:
> Deleting the OID does not remove the object itself - see details of
> lo_unlink() in Chapter 28 of the manuals.
>
> I seem to recall some other utilities in the contrib/ directory of the
> source distribution too.

Could it be `contrib/lo'? Here's some snippet from contrib/lo/README.lo:

[Snippet]
One of the problems with the JDBC driver (and this affects the ODBC driver
also), is that the specification assumes that references to BLOBS (Binary
Large OBjectS) are stored within a table, and if that entry is changed, the
associated BLOB is deleted from the database.

As PostgreSQL stands, this doesn't occur.  Large objects are treated as
objects in their own right; a table entry can reference a large object by
OID, but there can be multiple table entries referencing the same large
object OID, so the system doesn't delete the large object just because you
change or remove one such entry.

[...]

I've fixed this by creating a new data type 'lo', some support functions, a=
nd
a Trigger which handles the orphaning problem.  The trigger essentially jus=
t
does a 'lo_unlink' whenever you delete or modify a value referencing a larg=
e
object.  When you use this trigger, you are assuming that there is only one
database reference to any large object that is referenced in a
trigger-controlled column!
[/Snippet]

Regards.



From Premsun@nsasia.co.th Mon Oct  10 08:58:20 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C9C7DD9B66
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Oct 2005 05:58:20 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60279-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Oct 2005 08:58:17 +0000 (GMT)
Received: from mail.nsasia.co.th (mail.nsasia.co.th [203.146.6.34])
	by svr1.postgresql.org (Postfix) with ESMTP id 8CE85D9B50
	for <pgsql-sql@postgresql.org>; Mon, 10 Oct 2005 05:58:17 -0300 (ADT)
Received: from [192.168.200.39] (helo=mail.nsasia.co.th)
	by astaro.nsasia.co.th with esmtp (Exim 4.43) id 1EOtUB-00006T-9a
	for pgsql-sql@postgresql.org; Mon, 10 Oct 2005 15:58:19 +0700
Received: from Thailand-MTA by mail.nsasia.co.th
	with Novell_GroupWise; Mon, 10 Oct 2005 15:57:59 +0700
Message-Id: <434A8F85.C5F7.004C.0@nsasia.co.th>
X-Mailer: Novell GroupWise Internet Agent 7.0 
Date: Mon, 10 Oct 2005 15:57:53 +0700
From: "Premsun Choltanwanich" <Premsun@nsasia.co.th>
To: "Richard Huxton" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: How to delete Large Object from Database?
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
In-Reply-To: <434623FD.7000408@archonet.com>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part4D6F4901.1__="
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.074 required=5 tests=[AWL=0.017, HTML_30_40=0.056,
	HTML_MESSAGE=0.001]
X-Spam-Level: 
X-Archive-Number: 200510/77
X-Sequence-Number: 22930

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__Part4D6F4901.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

The lib I use is call lo_in and lo_out for manage BLOB.
=20
I understand that lo_unlink be related with lo_import and lo_export so I =
don't think that it work.


>>> Richard Huxton <dev@archonet.com> 07-Oct-05 14:30:05 pm >>>
Premsun Choltanwanich wrote:
>=20
> Dear All,
> =20
> I use '$libdir/lo' for manage my PostgreSQL Large Object. It work =
fine=20
> for me to get and put Large Object  from and to database. However I=20
> found something that may not correct when I try to backup my data. It=20
> seem that I cannot delete Large Object from database. It seem the =
thing=20
> I can do is only delete the reference oid from table but Object still =
in=20
> database.

> So,How to delete Large Object from Database? or Is my understanding =
wrong?

Deleting the OID does not remove the object itself - see details of=20
lo_unlink() in Chapter 28 of the manuals.

I seem to recall some other utilities in the contrib/ directory of the=20
source distribution too.

--=20
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


--=__Part4D6F4901.1__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dwindows-874=
">
<META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Microsoft Sans Serif">
<DIV>The lib I use is call lo_in and lo_out for manage BLOB.</DIV>
<DIV>&nbsp;</DIV>
<DIV>I understand that lo_unlink be related with lo_import and lo_export =
so I don't think that it work.</DIV><BR><BR>&gt;&gt;&gt; Richard Huxton =
&lt;dev@archonet.com&gt; 07-Oct-05 14:30:05 pm &gt;&gt;&gt;<BR>Premsun =
Choltanwanich wrote:<BR>&gt; <BR>&gt; Dear All,<BR>&gt;&nbsp; <BR>&gt; I =
use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine =
<BR>&gt; for me to get and put Large Object&nbsp; from and to database. =
However I <BR>&gt; found something that may not correct when I try to =
backup my data. It <BR>&gt; seem that I cannot delete Large Object from =
database. It seem the thing <BR>&gt; I can do is only delete the reference =
oid from table but Object still in <BR>&gt; database.<BR><BR>&gt; So,How =
to delete Large Object from Database? or Is my understanding wrong?<BR><BR>=
Deleting the OID does not remove the object itself - see details of =
<BR>lo_unlink() in Chapter 28 of the manuals.<BR><BR>I seem to recall some =
other utilities in the contrib/ directory of the <BR>source distribution =
too.<BR><BR>-- <BR>&nbsp;&nbsp; Richard Huxton<BR>&nbsp;&nbsp; Archonet =
Ltd<BR><BR>---------------------------(end of broadcast)-------------------=
--------<BR>TIP 9: In versions below 8.0, the planner will ignore your =
desire to<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; choose an index scan if =
your joining column's datatypes do not<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; match<BR></BODY></HTML>

--=__Part4D6F4901.1__=--



From dev@archonet.com Mon Oct  10 10:10:29 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 642A5D98A9
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Oct 2005 07:10:29 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 05538-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Oct 2005 10:10:24 +0000 (GMT)
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by svr1.postgresql.org (Postfix) with ESMTP id 50A72D97D5
	for <pgsql-sql@postgresql.org>; Mon, 10 Oct 2005 07:10:25 -0300 (ADT)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id B6B8F40C2DF; Mon, 10 Oct 2005 11:10:19 +0100 (BST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP id 8B55E15EDA;
	Mon, 10 Oct 2005 11:06:41 +0100 (BST)
Received: from mainbox.archonet.com ([127.0.0.1])
	by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 04522-05; Mon, 10 Oct 2005 11:06:38 +0100 (BST)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id 6873015ED5;
	Mon, 10 Oct 2005 11:06:38 +0100 (BST)
Message-ID: <434A3D2F.90907@archonet.com>
Date: Mon, 10 Oct 2005 11:06:39 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050912)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Premsun Choltanwanich <Premsun@nsasia.co.th>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to delete Large Object from Database?
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
	<434A8F85.C5F7.004C.0@nsasia.co.th>
In-Reply-To: <434A8F85.C5F7.004C.0@nsasia.co.th>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.058 required=5 tests=[AWL=0.008,
	FORGED_RCVD_HELO=0.05]
X-Spam-Level: 
X-Archive-Number: 200510/78
X-Sequence-Number: 22931

Premsun Choltanwanich wrote:
> 
> The lib I use is call lo_in and lo_out for manage BLOB.
>  
> I understand that lo_unlink be related with lo_import and lo_export so I 
> don't think that it work.

If you are using the contrib/lo library, then README.lo mentions:

* Some frontends may create their own tables, and will not create the
   associated trigger(s). Also, users may not remember (or know) to 
create the triggers.

Could this be the case with your database? There are a couple of other 
points in the README.lo that are worth checking too.

--
   Richard Huxton
   Archonet Ltd



From Premsun@nsasia.co.th Tue Oct  11 02:11:45 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C4B92D9F97
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 10 Oct 2005 23:11:45 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 45061-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 02:11:41 +0000 (GMT)
Received: from mail.nsasia.co.th (mail.nsasia.co.th [203.146.6.34])
	by svr1.postgresql.org (Postfix) with ESMTP id BA03BD9FE6
	for <pgsql-sql@postgresql.org>; Mon, 10 Oct 2005 23:11:42 -0300 (ADT)
Received: from [192.168.200.39] (helo=mail.nsasia.co.th)
	by astaro.nsasia.co.th with esmtp (Exim 4.43) id 1EP9cI-0007du-6t
	for pgsql-sql@postgresql.org; Tue, 11 Oct 2005 09:11:46 +0700
Received: from Thailand-MTA by mail.nsasia.co.th
	with Novell_GroupWise; Tue, 11 Oct 2005 09:11:31 +0700
Message-Id: <434B81B8.C5F7.004C.0@nsasia.co.th>
X-Mailer: Novell GroupWise Internet Agent 7.0 
Date: Tue, 11 Oct 2005 09:11:21 +0700
From: "Premsun Choltanwanich" <Premsun@nsasia.co.th>
To: "Richard Huxton" <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: How to delete Large Object from Database?
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
	<434A8F85.C5F7.004C.0@nsasia.co.th> <434A3D2F.90907@archonet.com>
In-Reply-To: <434A3D2F.90907@archonet.com>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part26042359.1__="
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.192 required=5 tests=[AWL=-0.072, HTML_30_40=0.056,
	HTML_MESSAGE=0.001, UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200510/79
X-Sequence-Number: 22932

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__Part26042359.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

The code that show below is refered to table and function that I use for =
kept BLOB (LO).
=20
CREATE TABLE t_data_pic
(
  "sysid" bigserial NOT NULL,
  data_sysid int8 NOT NULL,
  data_pic lo,
  CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")
)=20
WITH OIDS;
ALTER TABLE t_data_pic OWNER TO admin;
=20

CREATE OR REPLACE FUNCTION lo(oid)
  RETURNS lo AS
'$libdir/lo', 'lo'
  LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION lo(oid) OWNER TO postgres;
=20

CREATE OR REPLACE FUNCTION oid(lo)
  RETURNS oid AS
'$libdir/lo', 'lo_oid'
  LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION oid(lo) OWNER TO postgres;
=20

CREATE OR REPLACE FUNCTION lo_oid(lo)
  RETURNS oid AS
'$libdir/lo', 'lo_oid'
  LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION lo_oid(lo) OWNER TO postgres;
=20

CREATE OR REPLACE FUNCTION lo_in(cstring)
  RETURNS lo AS
'$libdir/lo', 'lo_in'
  LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION lo_in(cstring) OWNER TO postgres;
=20

CREATE OR REPLACE FUNCTION lo_out(lo)
  RETURNS cstring AS
'$libdir/lo', 'lo_out'
  LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION lo_out(lo) OWNER TO postgres;
=20


>>> Richard Huxton <dev@archonet.com> 10-Oct-05 17:06:39 pm >>>
Premsun Choltanwanich wrote:
>=20
> The lib I use is call lo_in and lo_out for manage BLOB.
> =20
> I understand that lo_unlink be related with lo_import and lo_export so =
I=20
> don't think that it work.

If you are using the contrib/lo library, then README.lo mentions:

* Some frontends may create their own tables, and will not create the
   associated trigger(s). Also, users may not remember (or know) to=20
create the triggers.

Could this be the case with your database? There are a couple of other=20
points in the README.lo that are worth checking too.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match


--=__Part26042359.1__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dwindows-874=
">
<META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Microsoft Sans Serif">
<DIV>The code that show below is refered to table and function that I use =
for kept BLOB (LO).</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE TABLE t_data_pic<BR>(<BR>&nbsp; "sysid" bigserial NOT =
NULL,<BR>&nbsp; data_sysid int8 NOT NULL,<BR>&nbsp; data_pic lo,<BR>&nbsp; =
CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")<BR>) <BR>WITH OIDS;<BR>ALT=
ER TABLE t_data_pic OWNER TO admin;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION lo(oid)<BR>&nbsp; RETURNS lo AS<BR>'$li=
bdir/lo', 'lo'<BR>&nbsp; LANGUAGE 'c' IMMUTABLE STRICT;<BR>ALTER FUNCTION =
lo(oid) OWNER TO postgres;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION oid(lo)<BR>&nbsp; RETURNS oid =
AS<BR>'$libdir/lo', 'lo_oid'<BR>&nbsp; LANGUAGE 'c' IMMUTABLE STRICT;<BR>AL=
TER FUNCTION oid(lo) OWNER TO postgres;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION lo_oid(lo)<BR>&nbsp; RETURNS oid =
AS<BR>'$libdir/lo', 'lo_oid'<BR>&nbsp; LANGUAGE 'c' IMMUTABLE STRICT;<BR>AL=
TER FUNCTION lo_oid(lo) OWNER TO postgres;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION lo_in(cstring)<BR>&nbsp; RETURNS lo =
AS<BR>'$libdir/lo', 'lo_in'<BR>&nbsp; LANGUAGE 'c' IMMUTABLE STRICT;<BR>ALT=
ER FUNCTION lo_in(cstring) OWNER TO postgres;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>CREATE OR REPLACE FUNCTION lo_out(lo)<BR>&nbsp; RETURNS cstring =
AS<BR>'$libdir/lo', 'lo_out'<BR>&nbsp; LANGUAGE 'c' IMMUTABLE STRICT;<BR>AL=
TER FUNCTION lo_out(lo) OWNER TO postgres;<BR></DIV>
<DIV>&nbsp;</DIV>
<DIV><BR><BR>&gt;&gt;&gt; Richard Huxton &lt;dev@archonet.com&gt; =
10-Oct-05 17:06:39 pm &gt;&gt;&gt;<BR>Premsun Choltanwanich wrote:<BR>&gt; =
<BR>&gt; The lib I use is call lo_in and lo_out for manage BLOB.<BR>&gt;&nb=
sp; <BR>&gt; I understand that lo_unlink be related with lo_import and =
lo_export so I <BR>&gt; don't think that it work.<BR><BR>If you are using =
the contrib/lo library, then README.lo mentions:<BR><BR>* Some frontends =
may create their own tables, and will not create the<BR>&nbsp;&nbsp; =
associated trigger(s). Also, users may not remember (or know) to <BR>create=
 the triggers.<BR><BR>Could this be the case with your database? There are =
a couple of other <BR>points in the README.lo that are worth checking =
too.<BR><BR>--<BR>&nbsp;&nbsp; Richard Huxton<BR>&nbsp;&nbsp; Archonet =
Ltd<BR><BR>---------------------------(end of broadcast)-------------------=
--------<BR>TIP 9: In versions below 8.0, the planner will ignore your =
desire to<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; choose an index scan if =
your joining column's datatypes do not<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; match<BR></DIV></BODY></HTML>

--=__Part26042359.1__=--



From tgl@sss.pgh.pa.us Tue Oct  11 03:01:14 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 70580D9CB2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 00:01:14 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 18395-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 03:01:11 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 88F6FD6F63
	for <pgsql-sql@postgresql.org>; Tue, 11 Oct 2005 00:01:10 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9B30rdA008633;
	Mon, 10 Oct 2005 23:00:53 -0400 (EDT)
To: "Premsun Choltanwanich" <Premsun@nsasia.co.th>
Cc: "Richard Huxton" <dev@archonet.com>, pgsql-sql@postgresql.org
Subject: Re: How to delete Large Object from Database? 
In-reply-to: <434B81B8.C5F7.004C.0@nsasia.co.th> 
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
	<434A8F85.C5F7.004C.0@nsasia.co.th> <434A3D2F.90907@archonet.com>
	<434B81B8.C5F7.004C.0@nsasia.co.th>
Comments: In-reply-to "Premsun Choltanwanich" <Premsun@nsasia.co.th>
	message dated "Tue, 11 Oct 2005 09:11:21 +0700"
Date: Mon, 10 Oct 2005 23:00:53 -0400
Message-ID: <8632.1128999653@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level: 
X-Archive-Number: 200510/80
X-Sequence-Number: 22933

"Premsun Choltanwanich" <Premsun@nsasia.co.th> writes:
> The code that show below is refered to table and function that I use for =
> kept BLOB (LO).
>  
> CREATE TABLE t_data_pic
> (
>   "sysid" bigserial NOT NULL,
>   data_sysid int8 NOT NULL,
>   data_pic lo,
>   CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")
> ) 
> WITH OIDS;
> ALTER TABLE t_data_pic OWNER TO admin;

Why am I not seeing any trigger attached to this table?  That lo_manage
trigger is the useful part of contrib/lo --- the separate data type is
mere window dressing.

			regards, tom lane



From Premsun@nsasia.co.th Tue Oct  11 03:10:53 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6E3DAD9CB2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 00:10:53 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 52700-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Oct 2005 03:10:51 +0000 (GMT)
Received: from mail.nsasia.co.th (mail.nsasia.co.th [203.146.6.34])
	by svr1.postgresql.org (Postfix) with ESMTP id E9A34D9FF2
	for <pgsql-sql@postgresql.org>; Tue, 11 Oct 2005 00:10:49 -0300 (ADT)
Received: from [192.168.200.39] (helo=mail.nsasia.co.th)
	by astaro.nsasia.co.th with esmtp (Exim 4.43) id 1EPAXQ-0000hn-54
	for pgsql-sql@postgresql.org; Tue, 11 Oct 2005 10:10:48 +0700
Received: from Thailand-MTA by mail.nsasia.co.th
	with Novell_GroupWise; Tue, 11 Oct 2005 10:10:33 +0700
Message-Id: <434B8F91.C5F7.004C.0@nsasia.co.th>
X-Mailer: Novell GroupWise Internet Agent 7.0 
Date: Tue, 11 Oct 2005 10:10:26 +0700
From: "Premsun Choltanwanich" <Premsun@nsasia.co.th>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Richard Huxton" <dev@archonet.com>, <pgsql-sql@postgresql.org>
Subject: Re: How to delete Large Object from Database?
References: <43465BAA.C5F7.004C.0@nsasia.co.th>
 <434623FD.7000408@archonet.com>
	<434A8F85.C5F7.004C.0@nsasia.co.th> <434A3D2F.90907@archonet.com>
	<434B81B8.C5F7.004C.0@nsasia.co.th><434B81B8.C5F7.004C.0@nsasia.co.th>
	<8632.1128999653@sss.pgh.pa.us>
In-Reply-To: <8632.1128999653@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: multipart/alternative; boundary="=__Part7F5D7A32.1__="
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.193 required=5 tests=[AWL=-0.071, HTML_30_40=0.056,
	HTML_MESSAGE=0.001, UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200510/81
X-Sequence-Number: 22934

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__Part7F5D7A32.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Sorry that I forgot to sent you trigger on my database.
=20
CREATE OR REPLACE FUNCTION lo_manage()
  RETURNS "trigger" AS
'$libdir/lo', 'lo_manage'
  LANGUAGE 'c' VOLATILE;
ALTER FUNCTION lo_manage() OWNER TO postgres;
=20


>>> Tom Lane <tgl@sss.pgh.pa.us> 11-Oct-05 10:00:53 am >>>
"Premsun Choltanwanich" <Premsun@nsasia.co.th> writes:
> The code that show below is refered to table and function that I use for =
=3D
> kept BLOB (LO).
> =20
> CREATE TABLE t_data_pic
> (
>   "sysid" bigserial NOT NULL,
>   data_sysid int8 NOT NULL,
>   data_pic lo,
>   CONSTRAINT t_data_pic_pkey PRIMARY KEY ("sysid")
> )=20
> WITH OIDS;
> ALTER TABLE t_data_pic OWNER TO admin;

Why am I not seeing any trigger attached to this table?  That lo_manage
trigger is the useful part of contrib/lo --- the separate data type is
mere window dressing.

regards, tom lane


--=__Part7F5D7A32.1__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dwindows-874=
">
<META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Microsoft Sans Serif">
<DIV>Sorry that I forgot to sent you trigger on my database.</DIV>
<DIV>&nbsp;</DIV>
<DIV>CREATE OR REPLACE FUNCTION lo_manage()<BR>&nbsp; RETURNS "trigger" =
AS<BR>'$libdir/lo', 'lo_manage'<BR>&nbsp; LANGUAGE 'c' VOLATILE;<BR>ALTER =
FUNCTION lo_manage() OWNER TO postgres;</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR><BR>&gt;&gt;&gt; Tom Lane &lt;tgl@sss.pgh.pa.us&gt; 11-Oct-05 =
10:00:53 am &gt;&gt;&gt;<BR>"Premsun Choltanwanich" &lt;Premsun@nsasia.co.t=
h&gt; writes:<BR>&gt; The code that show below is refered to table and =
function that I use for =3D<BR>&gt; kept BLOB (LO).<BR>&gt;&nbsp; <BR>&gt; =
CREATE TABLE t_data_pic<BR>&gt; (<BR>&gt;&nbsp;&nbsp; "sysid" bigserial =
NOT NULL,<BR>&gt;&nbsp;&nbsp; data_sysid int8 NOT NULL,<BR>&gt;&nbsp;&nbsp;=
 data_pic lo,<BR>&gt;&nbsp;&nbsp; CONSTRAINT t_data_pic_pkey PRIMARY KEY =
("sysid")<BR>&gt; ) <BR>&gt; WITH OIDS;<BR>&gt; ALTER TABLE t_data_pic =
OWNER TO admin;<BR><BR>Why am I not seeing any trigger attached to this =
table?&nbsp; That lo_manage<BR>trigger is the useful part of contrib/lo =
--- the separate data type is<BR>mere window dressing.<BR><BR>regards, tom =
lane<BR></DIV></BODY></HTML>

--=__Part7F5D7A32.1__=--