Re: pg_dump without blobs

From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: pg_dump without blobs
Date: 2007-07-17 08:22:21
Message-ID: f7hu87$2sp$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Joshua D. Drake skrev:
> Vivek Khera wrote:
>>
>> On Jul 16, 2007, at 9:26 AM, Francisco Reyes wrote:
>>
>>> I guess the next question is 'what does postgresql considers a blob'?
>>> bytea fields? How about a large text with megabytes worth of data?
>>
>> bytea and text fields are NOT blobs. they are what you access via the
>> 'large object' functions.
>
> To follow up on this.
>
> In oracle large text CLOB and binary objects BLOB are synonomous (I
> believe) with PostgreSQL TEXT and BYTEA.

I don't have experience with Oracle versions >8, but back then this was
not the case. Back then, Oracles LOBs had a special syntax for access,
quite similar to the one documented here:

http://www.postgresql.org/docs/8.2/interactive/largeobjects.html

> PostgreSQL also supports a non standard, and frankly better
> implementation called lo for binary data, which also uses BYTEA data but
> breaks it up to make it more efficient per row.

What I would really like would be for the two models to be merged - a LO
should be a datatype in its own right, having all the same functionality
as BYTEA, plus the additional interface. As you say, this is an
additional implementation, and the programmer shouldn't need additional
syntax unless it needs the additional functionality.

So in my dream world, you could do:

CREATE TABLE image (
name text,
raster LOB
);

INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/tmp/image.gif'));

INSERT INTO image (name, raster)
VALUES ('beautiful image 2', '
_
/ \
|O|
\_/
ASCII ART RULEZ'));

SELECT name, raster FROM image
WHERE name = 'beautiful image';

SELECT name, lo_export(raster, '/tmp/ascii.txt') FROM image
WHERE name = 'beautiful image 2';

I realize that there may be all kinds of implementation problems with
this - but this is how it 'ought' to work, IMO.

Nis

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tim Tassonis 2007-07-17 09:46:00 Re: The speed problem of Varchar vs. Char
Previous Message Hannes Dorbath 2007-07-17 07:52:27 Re: PHP pg_connect