Thread: How to delete Large Object from Database?
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.
A Detail shown below is reason I think that I cannot delete Large Object out of database.
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)
So,How to delete Large Object from Database? or Is my understanding wrong?
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.
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.
>>> 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
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;
(
"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;
CREATE OR REPLACE FUNCTION lo(oid)
RETURNS lo AS
'$libdir/lo', 'lo'
LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION lo(oid) OWNER TO postgres;
CREATE OR REPLACE FUNCTION oid(lo)
RETURNS oid AS
'$libdir/lo', 'lo_oid'
LANGUAGE 'c' IMMUTABLE STRICT;
ALTER FUNCTION oid(lo) OWNER TO postgres;
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;
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;
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;
>>> Richard Huxton <dev@archonet.com> 10-Oct-05 17:06:39 pm >>>
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
---------------------------(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" <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
Sorry that I forgot to sent you trigger on my database.
CREATE OR REPLACE FUNCTION lo_manage()
RETURNS "trigger" AS
'$libdir/lo', 'lo_manage'
LANGUAGE 'c' VOLATILE;
ALTER FUNCTION lo_manage() OWNER TO postgres;
RETURNS "trigger" AS
'$libdir/lo', 'lo_manage'
LANGUAGE 'c' VOLATILE;
ALTER FUNCTION lo_manage() OWNER TO postgres;
>>> 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 =
> 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