pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to delete Large Object from Database?
8+ messages / 4 participants
[nested] [flat]
* How to delete Large Object from Database?
@ 2005-10-07 04:27 Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Premsun Choltanwanich @ 2005-10-07 04:27 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
@ 2005-10-07 07:30 ` Richard Huxton <dev@archonet.com>
2005-10-08 11:20 ` Re: How to delete Large Object from Database? Volkan YAZICI <volkan.yazici@gmail.com>
2005-10-10 08:57 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 2 replies; 8+ messages in thread
From: Richard Huxton @ 2005-10-07 07:30 UTC (permalink / raw)
To: Premsun Choltanwanich <Premsun@nsasia.co.th>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
@ 2005-10-08 11:20 ` Volkan YAZICI <volkan.yazici@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Volkan YAZICI @ 2005-10-08 11:20 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Premsun Choltanwanich <Premsun@nsasia.co.th>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
@ 2005-10-10 08:57 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-10 10:06 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Premsun Choltanwanich @ 2005-10-10 08:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-10 08:57 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
@ 2005-10-10 10:06 ` Richard Huxton <dev@archonet.com>
2005-10-11 02:11 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2005-10-10 10:06 UTC (permalink / raw)
To: Premsun Choltanwanich <Premsun@nsasia.co.th>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-10 08:57 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-10 10:06 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
@ 2005-10-11 02:11 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-11 03:00 ` Re: How to delete Large Object from Database? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Premsun Choltanwanich @ 2005-10-11 02:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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;
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-10 08:57 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-10 10:06 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-11 02:11 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
@ 2005-10-11 03:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-11 03:10 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-10-11 03:00 UTC (permalink / raw)
To: Premsun Choltanwanich <Premsun@nsasia.co.th>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to delete Large Object from Database?
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-10 08:57 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-10 10:06 ` Re: How to delete Large Object from Database? Richard Huxton <dev@archonet.com>
2005-10-11 02:11 ` Re: How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-11 03:00 ` Re: How to delete Large Object from Database? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-11 03:10 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
0 siblings, 0 replies; 8+ messages in thread
From: Premsun Choltanwanich @ 2005-10-11 03:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
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;
>>> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-10-11 03:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-07 04:27 How to delete Large Object from Database? Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-07 07:30 ` Richard Huxton <dev@archonet.com>
2005-10-08 11:20 ` Volkan YAZICI <volkan.yazici@gmail.com>
2005-10-10 08:57 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-10 10:06 ` Richard Huxton <dev@archonet.com>
2005-10-11 02:11 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
2005-10-11 03:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-11 03:10 ` Premsun Choltanwanich <Premsun@nsasia.co.th>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox