Automatically deleting dangling large object references.

Поиск
Список
Период
Сортировка
Искать

Automatically deleting dangling large object references.

От:
"Robert B. Easter" <reaster@comptechnews.com>
Дата:

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

Re: Automatically deleting dangling large object references.

От:
"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).

Newbie DB problem

От:
Keith Grennan <k.grennan@jach.hawaii.edu>
Дата:
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.

Re: Newbie DB problem

От:
Travis Bauer <trbauer@indiana.edu>
Дата:
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.   
>
FAQ