Re: [HACKERS] Re: [INTERFACES] New pg_type for large object - Mailing list pgsql-interfaces

Peter T Mount wrote:

> On Thu, 9 Apr 1998, David Hartwig wrote:
>
> > Greetings,
> >
> > We are putting the finishing touches on some enhancements to the ODBC
> > driver.   One  feature, in particular, uses large objects to implement
> > OLE data types.   We are rather please with the way it is working.   Via
> > MS Access, we have been able to INSERT and SELECT objects, such as VISIO
> > drawings, Word Documents, and WAV sound clips.    However, we've run
> > into two problems.
> >
> > The first is, that when we update the OID which points to the large
> > object, the large object is orphaned.  I realize that at the time of the
> > update, we could select the old OID and subsequently drop the large
> > object.  The problem is that general purpose tools such as MS Access do
> > not provide an clean framework for invoking such a query.
> > Specifically, UPDATE statements would have to be torn apart to build
> > such a SELECT statement.  In the short term I can build a separate
> > daemon to track down the orphans.   I hope VACUUM will eventually handle
> > these.
> >
> > The second, and more difficult, problem is that there is no large object
> > data type.  When we gather table info in the driver we have no idea that
> > an OID may actually be a large object.   What we need is a large object
> > data type.  Furthermore, the data type must have a stable OID so the we
> > can recognize it when we gather table info.  We have tested the driver
> > by creating our own date type.  However, with the existing function
> > scoping of our driver, it is extremely difficult to dynamically locate a
> > user defined large object data type.    So for testing we have compiled
> > in our "lo" data type OID.
> >
> > What I would like to know is, can a large object data type be added as
> > an internal data type?    The various "lo_" functions should eventually
> > be overloaded (or modified) to be able to use this data type.   But it
> > is not necessary at this time.  I believe this addition is a very low
> > risk change, and I would very much like to get to have it in the 6.3.2
> > release for distribution.   May I submit the patch, or would someone
> > kindly hack it in for us?
>
> I've actually started to look at this for JDBC, as it too has the orphan
> problem. I went down two routes. One using triggers, but that had the
> problem that triggers are not inherited, so I started to look at rules.
>
> However, as usual, my pay job had to take precidence, so I was about to
> start looking at it today.
>
> I'd like to see your solution to this.

We are going to wait to get a  large object data type built into 6.4.    In the
meantime we are going to require the DBA to create an "lo" data type in the
database.  We will include the SQL create script as part of the driver release.
Then, we'll query the database for the oid of the "lo" data type at connect
time.   Not very elegant, but it get the job done until 6.4.

As far as those lo orphans go, we'll will put together a cleanup script. to
search for "lo" attributes in each database and make sure that something points
each large object in pg_class.   We will have to distribute this script as part
of the ODBC package to be run at some interval on the server.    Eventually, it
would seems, that this should be part of the VACUUM process.Marc,Any word on when
this ODBC this solution will be available.