Date: Wed, 17 Jul 2002 00:20:30 -0700
From: Dror Matalon <dror@zapatec.com>
To: PostgreSQL Mailing Lists-SQL <pgsql-sql@postgresql.org>
Subject: Large objects and Meta Data
Message-ID: <20020717072030.GT86089@four.zapatec.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi,

I'm handling large objects from JDBC in a servlet environment. Every
time we create a lo we also create a row in the zp_file table that
holds additional information about the file, as well as the oid of
the large object:

Column  |           Type           |   Modifiers
---------+--------------------------+---------------
lo_oid  | oid                      | not null
name    | character varying(1024)  |
created | timestamp with time zone | default 'now'
type    | character varying(256)   |


Whenever I "attach" an object to a file I need to point an oid from
that table to the row in the zp_file table which in turn points to
the lo.

For instance I have the following test_up table:

Column |     Type      | Modifiers
--------+---------------+-----------
first  | character(20) |
last   | character(20) |
pic    | oid           |


So whenever I create a row in test_up that "includes" a file/blob/lo,
I first create the lo, then create the zp_file row that contains the
meta info about the large object, and finally point the oid of the
pic field to the row in the zp_file table.

I am planning on adding foreign keys from pic to zp_file(oid) and
from lo_oid to pg_largeobject(oid).

I've also put the zp_file and blob creation in a transaction for both
creation and when I drop the large object. 

Since this feels quite complicated, I was wondering if I'm missing
any obvious shortcuts or other ways of doing this.

Thanks,

Dror

-- 
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com