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

Re: [HACKERS] text should be a blob field

From: Peter T Mount <psqlhack(at)maidast(dot)demon(dot)co(dot)uk>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: Zeugswetter Andreas SARZ <Andreas(dot)Zeugswetter(at)telecom(dot)at>, pgsql-hackers(at)hub(dot)org, PostgreSQL-questions <questions(at)postgreSQL(dot)org>
Subject: Re: [HACKERS] text should be a blob field
Date: 1998-03-03 20:38:24
Message-ID: Pine.LNX.3.95.980303195117.25913C-100000@maidast (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Tue, 3 Mar 1998, Bruce Momjian wrote:

> > make text a blob datatype (maybe storing <= 8k row with tuple, >=8k in blob
> > tablespace)
> Hmmm.

There was some talk about this about a month ago.

Although we now have blob support in the JDBC driver, there is one
outstanding issue with them, that I was waiting for 6.3 to be released
before starting on it (and almost certainly starting a discussion here
about it).

Allowing text to use blobs for values larger than the current block size
would hit the same problem.

Ok, here's what the problem is at the moment:

The JDBC example ImageViewer uses a table to store the name of an image,
and the OID of the associated blob.

# create table images (imgname name,imgoid oid);

Ok, we now create an entry in the table for an image with:

# insert into images values ('test.gif',lo_import('/home/pmount/test.gif'));

This is fine so far. Now say we delete that row with:

# delete from images where name = 'test.gif';

Fine again, except that the blob is still in the database. To get round
this, you would have to add extra statements to handle this, and for JDBC,
there is no standard way to do this.

What I was thinking of, was to create a new type 'blob' which would delete
the associated large object when the row is deleted. However, here's the
problems against this:

1. Is there a call made by the backend to each datatype when a row is 
   deleted? I can't see one.

2. When we update a row, we don't want the overhead of copying a very
   large blob when a row is first copied, then the original deleted, etc.

Anyhow, I'm thinking of various ways around this - just don't hold your
breath ;-)

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: Brett McCormickDate: 1998-03-03 21:43:33
Subject: no operator '=' for types char16 and text
Previous:From: Peter T MountDate: 1998-03-03 19:48:10
Subject: Re: [HACKERS] backend -> interface communication

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