Thread: Automatically deleting dangling large object references.

I was thinking of trying something like the following to automatically delete
rows from tables that reference large objects when the large object is
unlinked.  It would depend on pg_class having a PRIMARY key (on the hidden
'oid' column in this possible example):

-- this is a generic table to just hold large objects
CREATE TABLE lobjs (
    id        SERIAL    CONSTRAINT lobjs_id_con
        PRIMARY KEY,
        -- other tables referencing this would also react.
     lo_oid        OID    CONSTRAINT lobjs_lo_oid_con NOT NULL,
        -- holds an actual large object id.
     lo_ref_oid    OID    CONSTRAINT lobjs_lo_ref_oid_con
            REFERENCES pg_class
            ON UPDATE CASCADE
            ON DELETE CASCADE
            INITIALLY DEFERRED
);

CREATE RULE set_lo_ref_oid AS
    ON INSERT TO lobjs
    DO
        UPDATE lobjs
        SET lo_ref_oid = pg_class.oid
        FROM pg_class
        WHERE pg_class.relname = "xinx" || new.lo_oid;

Something like that (I'd have to check this sql to make sure its right). I don't
think there is a primary key on the system tables.  I wonder if it would cause
problems if there where?

Anyway, does anyone know of a way to accomplish something like what I am trying
to do with the large objects?

--
Robert B. Easter
reaster@comptechnews.com

On Tue, 02 May 2000, Robert B. Easter wrote:
> I was thinking of trying something like the following to automatically delete
> rows from tables that reference large objects when the large object is
> unlinked.  It would depend on pg_class having a PRIMARY key (on the hidden
> 'oid' column in this possible example):
[snip]

Nevermind, at least I can do the reverse - automatically unlink a large object
when the row that holds its oid is deleted:

CREATE RULE unlinkit AS
ON DELETE TO largeobjtable
DO SELECT lo_unlink(old.lgobjoid) FROM largeobjtable
WHERE largeobjtable.lgobjoid = old.lgobjoid;

This SELECT doesn't really do anything but call that lo_unlink function.
Kinda strange.  Can anyone see any problems with doing something like this in a
production database?  I'd like to know more about how people handle large
object consistency.

(newbie stuff, sorry).

Hi,

I'm a co-op student at the Joint Astronomy Centre in Hilo, Hawaii.  I got
hired to organize ALL of the company's technical documentation into a
single searchable database.  The only direction my boss gave me for this
project was "you should use Postgres".  So now I'm left to propose and
implement a DB that will allow users to add documents, do
keyword/author/title searches, and display the found documents on the web.
This must be a fairly common problem.  Is there a standard database
structure for this?   Are there any good books or online resources that
might give me some ideas? I realize that this is a pretty general
databasing question, but I'm not sure where to start. Thanks in advance
for your help.

Keith Grennan.

Keith,

As you might imagine, this is a huge area of research. You might
take a look at a book called "How Search Engines Work."  It is written by
a professor at the Universtiy of Tenessee and gives a good introduction to
the standard data structures/algorithms for such things.

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Wed, 3 May 2000, Keith Grennan wrote:

> Hi,
>
> I'm a co-op student at the Joint Astronomy Centre in Hilo, Hawaii.  I got
> hired to organize ALL of the company's technical documentation into a
> single searchable database.  The only direction my boss gave me for this
> project was "you should use Postgres".  So now I'm left to propose and
> implement a DB that will allow users to add documents, do
> keyword/author/title searches, and display the found documents on the web.
> This must be a fairly common problem.  Is there a standard database
> structure for this?   Are there any good books or online resources that
> might give me some ideas? I realize that this is a pretty general
> databasing question, but I'm not sure where to start. Thanks in advance
> for your help.
>
> Keith Grennan.
>