Blob with OIDs and trigger

#1

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.

Thanks a lot
Phil

#2

Philipp Kraus, 18.11.2009 15:01:

Quote:

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.

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

#3

On 2009-11-18 15:16:04 +0100, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Quote:

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)

#4

Philipp Kraus, 18.11.2009 15:42:

Quote:

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

#5

Thomas Kellerer, 18.11.2009 16:06:

Quote:

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

#6

On 2009-11-18 16:06:48 +0100, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Quote:

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

Why don't you use bytea? Makes live a lot easier

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

#7

Philipp Kraus, 18.11.2009 16:26:

Quote:

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

Did you install the module?

#8

On 2009-11-18 16:48:55 +0100, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Quote:

Philipp Kraus, 18.11.2009 16:26:
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

Did you install the module?

I had installed the binary package for OSX and created the
plpsql language into my database. Do I have forgot something?

#9

Philipp Kraus, 18.11.2009 16:55:

Quote:

On 2009-11-18 16:48:55 +0100, Thomas Kellerer
OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Philipp Kraus, 18.11.2009 16:26:
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

Did you install the module?

I had installed the binary package for OSX and created the
plpsql language into my database. Do I have forgot something?

Yes, you need to install the contrib module "lo".

The script should be located in share/contrib

Thomas

#10

On 2009-11-18 17:15:58 +0100, Thomas Kellerer
<OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Quote:

Philipp Kraus, 18.11.2009 16:55:
On 2009-11-18 16:48:55 +0100, Thomas Kellerer
OTPXDAJCSJVU (AT) spammotel (DOT) com> said:

Philipp Kraus, 18.11.2009 16:26:
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

Did you install the module?

I had installed the binary package for OSX and created the
plpsql language into my database. Do I have forgot something?

Yes, you need to install the contrib module "lo". The script should be
located in share/contrib

Thanks. It works now