Blob with OIDs and trigger
Philipp Kraus
I have create a table with this structur
CREATE TABLE import
(
id1 integer NOT NULL,
id2 integer NOT NULL,
dataset oid NOT NULL,
CONSTRAINT import_pk PRIMARY KEY (id1, id2),
CONSTRAINT import_fk FOREIGN KEY (id1, id1)
REFERENCES ... MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
I insert my blobs with JDBC and I can read / write them without problems.
Must I delete explicite the blob, if I delete a row on my table "import" ?
If yes, how? I would like to do this with a trigger on my table.
Thanks a lot
Phil
Thomas Kellerer
My understanding is that you need to take care of deleting the large objects yourself.
Unless you have BLOBs > 1GB and/or you need to access only parts of the BLOBs (e.g. bytes 42 to 56) you can use bytea columns instead. They are easy to read and write in JDBC (set/getBinaryStream) and you don't need to worry about housekeeping.
Thomas
Philipp Kraus
<OTPXDA...@spammotel.com> said:
> Philipp Kraus, 18.11.2009 15:01:
>> Hi,
>>
>> I have create a table with this structur
>>
>> CREATE TABLE import
>> (
>> id1 integer NOT NULL,
>> id2 integer NOT NULL,
>> dataset oid NOT NULL,
>> CONSTRAINT import_pk PRIMARY KEY (id1, id2),
>> CONSTRAINT import_fk FOREIGN KEY (id1, id1)
>> REFERENCES ... MATCH SIMPLE
>> ON UPDATE CASCADE ON DELETE CASCADE
>> )
>> WITH (OIDS=FALSE);
>>
>> I insert my blobs with JDBC and I can read / write them without problems.
>>
>> Must I delete explicite the blob, if I delete a row on my table "import" ?
>> If yes, how? I would like to do this with a trigger on my table.
>
> My understanding is that you need to take care of deleting the large
> objects yourself.
I understand that also, but how can I delete the binary data in a
trigger function in the table?
I would like to run a trigger on update / delete that delete the binary
data (if it is required)
Thomas Kellerer
> I understand that also, but how can I delete the binary data in a
> trigger function in the table?
> I would like to run a trigger on update / delete that delete the binary
> data (if it is required)
Maybe this helps:
http://www.postgresql.org/docs/8.4/static/lo.html
Why don't you use bytea? Makes live a lot easier
Thomas
Thomas Kellerer
> Philipp Kraus, 18.11.2009 15:42:
>> I understand that also, but how can I delete the binary data in a
>> trigger function in the table?
>> I would like to run a trigger on update / delete that delete the
>> binary data (if it is required)
>
> Maybe this helps:
> http://www.postgresql.org/docs/8.4/static/lo.html
Oh and: http://www.postgresql.org/docs/8.4/static/vacuumlo.html
Philipp Kraus
<OTPXDA...@spammotel.com> said:
I have take the example under 8.3 but it dosn't work
CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
The command "lo_manage" isn't known by the database
Thomas Kellerer
> I have take the example under 8.3 but it dosn't work
>
> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON image
> FOR EACH ROW EXECUTE PROCEDURE lo_manage(raster);
>
> The command "lo_manage" isn't known by the database
>
Philipp Kraus
<OTPXDA...@spammotel.com> said:
I had installed the binary package for OSX and created the
plpsql language into my database. Do I have forgot something?
Thomas Kellerer
The script should be located in share/contrib
Thomas
Philipp Kraus
Laurenz Albe
Yes; here is an example:
test=> CREATE TABLE lo_test (id integer PRIMARY KEY, lob oid);
test=> CREATE OR REPLACE FUNCTION lo_trigger() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
IF OLD.lob IS NOT NULL THEN
IF TG_OP = 'DELETE' THEN
PERFORM lo_unlink(OLD.lob);
ELSEIF (NEW.lob IS NULL OR OLD.lob != NEW.lob) THEN
PERFORM lo_unlink(OLD.lob);
END IF;
END IF;
END IF;
IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;$$;
test=> CREATE TRIGGER lo_trigger AFTER UPDATE OR DELETE ON lo_test
FOR EACH ROW EXECUTE PROCEDURE lo_trigger();
test=> \lo_import schiach.jpg
lo_import 20337
test=> INSERT INTO lo_test (id, lob) VALUES (1, 20337);
INSERT 0 1
test=> \lo_list
Large objects
ID | Description
-------+-------------
20337 |
(1 row)
test=> DELETE FROM lo_test WHERE id=1;
DELETE 1
test=> \lo_list
Large objects
ID | Description
----+-------------
(0 rows)
Yours,
Laurenz Albe