Skip site navigation (1) Skip section navigation (2)

Re: [QUESTIONS] Re: [HACKERS] text should be a blob field

From: Peter T Mount <psqlhack(at)maidast(dot)demon(dot)co(dot)uk>
To: Jan Wieck <jwieck(at)debis(dot)com>
Cc: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>, Andreas(dot)Zeugswetter(at)telecom(dot)at, pgsql-hackers(at)hub(dot)org
Subject: Re: [QUESTIONS] Re: [HACKERS] text should be a blob field
Date: 1998-03-15 13:25:02
Message-ID: Pine.LNX.3.95.980315132008.4870B-100000@maidast (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Wed, 4 Mar 1998, Jan Wieck wrote:

> Bruce wrote:
> > > 1. Is there a call made by the backend to each datatype when a row is
> > >    deleted? I can't see one.
> >
> > Well, you could have a RULE that deletes the large object at row
> > deletion time.  However, if two rows point to the same large object, the
> > first one deleting it would delete the large object for the other.  The
> > only solution to this is to have a separate large object table, and use
> > reference counts so only the last user of the object deletes it.
>     I think triggers are more appropriate.
>     On INSERT check that the large object referenced exists.
>     On  UPDATE  if large object reference changes, check that new
>     large object exists and  check  if  old  large  object  isn't
>     referenced  any more in which case drop the old large object.
>     On DELETE check if large object isn't referenced any more ...
>     Yes - I like triggers :-)

I'm begining to agree with you here.

So far, I've got the trigger to work, so if a row of a table is deleted,
or an oid referencing a BLOB is updated, then the old BLOB is deleted.
This removes the orphaned BLOB problem.

The only problem I have now, is:

  How to get a trigger to be automatically created on a table when the
  table is created. This would be required, so the end user doesn't have
  to do this (normally from within an application).

This would be required, esp. for expanding the text type (or memo, or

Any Ideas?

Peter T Mount  petermount(at)earthling(dot)net or pmount(at)maidast(dot)demon(dot)co(dot)uk
Main Homepage:
Work Homepage: Work EMail: peter(at)maidstone(dot)gov(dot)uk

In response to


pgsql-hackers by date

Next:From: Maurice GittensDate: 1998-03-15 14:29:06
Subject: Re: [QUESTIONS] Re: [HACKERS] text should be a blob field
Previous:From: Peter T MountDate: 1998-03-15 13:16:52
Subject: Re: [QUESTIONS] using composite types

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group