I am developing a PostgreSQL interface for VisualWorks Smalltalk. I have
all the basics going, and I'm truing to get BLOB support sorted out. The
are two components to the interface, the first (which I call the driver) is
an implementation of the frontend/backend protocol which maps a closely as
possible to the manual pages , so there are classes for all the different
kinds of message and variables etc. Then second component is a mapping of
the VisualWorks EXDI (EXternal Database Interface) to PostgreSQL. The EXDI
part sits on top of the driver.
I can manipulate BLOBs from Smalltalk using the driver layer. This involves
calling the lo_x family of functions, and it all seems to work well.
Now, to the problem ... The definition of the EXDI layer demands that people
are able to supply BLOB data as part of a regular insert or update. The
mapping layer I'm writing is told 'here is a ByteArray - convert it to a
form that's OK for SQL'. The EXDI works this way because it suits the
Oracles and Sybases of this world - they *do* allow the bytes to be embedded
in the SQL (or so my reading of the code suggests).
What I do when given a ByteArray is to grab the database connection (another
story in itself) and use the lo_* functions to create the BLOB and write the
ByteArray to PostgreSQL. Then I return the string form of the BLOBs oid to
be included in the SQL. And it works!! The database is updated as
expected. The problem is that I'm making new BLOBs for both inserts and
updates, but not getting rid of any old ones. When converting a ByteArray I
do not have access to schema information, so I don't know what table is
being updated, nor what the oid of the row is, or what the primary key is -
so I can't look up the old.oid(s).
To get around the problem of old BLOBs I'm thinking that I can just get
people to set up a rule (or function or trigger) that will delete the old
blob as the new one is being written. It seems that this should be
possible, but I can't get it to work.
Following are a table and rule create statement & the error I get when I use
them. The questions are: am I trying to do something insane? Am I tackling
it in a reasonable way? and, what's wrong with what's below?
Many thanks ...
=========================================
[bbadger@catbert bbadger]$ pg_dump test1
\connect - bbadger
CREATE TABLE "exditest30" ("name" character varying(30),"blobdata" oid
);
COPY "exditest30" FROM stdin;
Curly 100524
Moe 100540
Larry 100556
\.
CREATE RULE exditest30_update AS ON UPDATE TO exditest30 DO SELECT
lo_unlink(old.blobdata) AS lo_unlink;
[bbadger@catbert bbadger]$
==========================================
test1=# test1=# update exdiTest30 set blobdata = 100524 where name = 'Moe';
ERROR: parser: parse error at or near "test1"
test1=# NOTICE: Caution: DROP TABLE cannot be rolled back, so don't abort
now
test1'# NOTICE: Caution: DROP INDEX cannot be rolled back, so don't abort
now
test1-# NOTICE: mdopen: couldn't open xinv100540: No such file or directory
test1'# NOTICE: RelationIdBuildRelation: smgropen(xinv100540): No such file
or directory
test1'# NOTICE: mdopen: couldn't open xinv100540: No such file or directory
test1-# NOTICE: Caution: DROP TABLE cannot be rolled back, so don't abort
now
test1'# ERROR: RelationClearRelation: relation 100540 deleted while still
in use
test1'# test1=# commit;
test1'# COMMIT