Обсуждение: How to delete Large Object from Database?

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

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, and
a Trigger which handles the orphaning problem.  The trigger essentially just
does a 'lo_unlink' whenever you delete or modify a value referencing a large
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.

>>> Richard Huxton <dev@archonet.com> 07-Oct-05 14:30:05 pm >>>
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

---------------------------(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

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

"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