Re: [SQL] How to delete Large Object from Database?

2005-10-10 Thread Premsun Choltanwanich 


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

Re: [SQL] How to delete Large Object from Database?

2005-10-10 Thread Richard Huxton 

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

Re: [SQL] How to delete Large Object from Database?

2005-10-10 Thread Premsun Choltanwanich 


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

Re: [SQL] How to delete Large Object from Database?

2005-10-10 Thread Tom Lane 
"Premsun Choltanwanich" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] How to delete Large Object from Database?

2005-10-10 Thread Premsun Choltanwanich 


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 <[EMAIL PROTECTED]> 11-Oct-05 10:00:53 am >>>"Premsun Choltanwanich" <[EMAIL PROTECTED]> 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_managetrigger is the useful part of contrib/lo --- the separate data type ismere window dressing.regards, tom lane