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?

Great work!
Thu, 09 Apr 1998 18:54:24 -0400
David Hartwig <daveh@insightdist.com>
New pg_type for large object
> 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'm not certain exactly what you want (didn't read very closely and it
doesn't fall in an area I've worked with) but it is not likely to be in
v6.3.2 since we're already in the freeze period. However, I would
suggest revisiting the subject just after the release, perhaps roping in
others who have worked with large objects (Peter Mount comes to mind).

There will be a ~2 month period for working on new capabilities, and
this might fit into that.

                       - Tom

Fri, 10 Apr 1998 01:18:24 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] New pg_type for large object
> 
> > 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'm not certain exactly what you want (didn't read very closely and it
> doesn't fall in an area I've worked with) but it is not likely to be in
> v6.3.2 since we're already in the freeze period. However, I would
> suggest revisiting the subject just after the release, perhaps roping in
> others who have worked with large objects (Peter Mount comes to mind).
> 
> There will be a ~2 month period for working on new capabilities, and
> this might fit into that.

Yes, agreed.  And it is a good topic to discuss.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 9 Apr 1998 22:10:34 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] New pg_type for large object
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.

-- 
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk


Fri, 10 Apr 1998 11:06:34 +0100 (BST)
Peter T Mount <pgint@maidast.demon.co.uk>
Re: [INTERFACES] New pg_type for large object
>>>>> "daveh" == David Hartwig <daveh@insightdist.com> writes:

    > This is a multi-part message in MIME format.
    > --------------493C6ADCB141A4B0F7C01648 Content-Type: text/plain;
    > charset=us-ascii Content-Transfer-Encoding: 7bit

    > 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.

You should be able to use triggers to fix the problem at the time that 
the update statement is run.

    > 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?

    > Great work!


    > --------------493C6ADCB141A4B0F7C01648 Content-Type:
    > text/x-vcard; charset=us-ascii; name="vcard.vcf"
    > Content-Transfer-Encoding: 7bit Content-Description: Card for
    > David Hartwig Content-Disposition: attachment;
    > filename="vcard.vcf"

    > begin: vcard fn: David Hartwig n: Hartwig;David email;internet:
    > daveh@insightdist.com x-mozilla-cpt: ;0 x-mozilla-html: FALSE
    > version: 2.1 end: vcard


    > --------------493C6ADCB141A4B0F7C01648--

-- 
Kent S. Gordon
Architect
iNetSpace Co.
voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com

Fri, 10 Apr 1998 10:32:38 -0500 (CDT)
"Kent S. Gordon" <kgor@inetspace.com>
Re: [HACKERS] New pg_type for large object
On Fri, 10 Apr 1998, Kent S. Gordon wrote:

[snip]

>     > 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.
> 
> You should be able to use triggers to fix the problem at the time that 
> the update statement is run.

Yes that is one possibility, which I have done here, but this is a
generic problem, rather than one unique to a single application.

For triggers to work, you would have to add the trigger to each table, and
to each column that may contain a large object. Also, triggers are not
inherited.

Creating a new lo/blob data type would make this transparent to the user,
and would permit already written JDBC or ODBC based applications for other
databases to work without modification.

-- 
Peter T Mount  petermount@earthling.net or pmount@maidast.demon.co.uk
Main Homepage: http://www.demon.co.uk/finder
Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk


Fri, 10 Apr 1998 19:48:29 +0100 (BST)
Peter T Mount <psqlhack@maidast.demon.co.uk>
Re: [HACKERS] New pg_type for large object
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.
Mon, 13 Apr 1998 10:29:17 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Re: [INTERFACES] New pg_type for large object
On Mon, 13 Apr 1998, David Hartwig wrote:

> Marc,Any word on when this ODBC this solution will be available.

	Source code replaced...have to do the readme files and whatnot
tonight from home...submit patches to me as appropriate, and, of course,
monitor the interfaces mailing list...




Mon, 13 Apr 1998 11:05:53 -0400 (EDT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [INTERFACES] New pg_type for large object
The Hermit Hacker wrote:

> On Mon, 13 Apr 1998, David Hartwig wrote:
>
> > Marc,Any word on when this ODBC this solution will be available.
>
>         Source code replaced...have to do the readme files and whatnot
> tonight from home...submit patches to me as appropriate, and, of course,
> monitor the interfaces mailing list...

Marc,

Did you get the README.TXT I sent to you last week?   Will resend or revise
if necessary.

Also, I need to know when you took (or will take) the last snapshot from our
page, so that I know our sources will be in sync.

What is the target date for the 6.3.2 cut?   I would like to get our latest
snapshot in that release.
Mon, 13 Apr 1998 12:10:58 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Re: [INTERFACES] New pg_type for large object
On Mon, 13 Apr 1998, David Hartwig wrote:

> 
> 
> The Hermit Hacker wrote:
> 
> > On Mon, 13 Apr 1998, David Hartwig wrote:
> >
> > > Marc,Any word on when this ODBC this solution will be available.
> >
> >         Source code replaced...have to do the readme files and whatnot
> > tonight from home...submit patches to me as appropriate, and, of course,
> > monitor the interfaces mailing list...
> 
> Marc,
> 
> Did you get the README.TXT I sent to you last week?   Will resend or revise
> if necessary.

	Got it, but its in my mailbox at home, so will add it later
tonight, unless you want to resend it to me...

> Also, I need to know when you took (or will take) the last snapshot from our
> page, so that I know our sources will be in sync.

	Best thing to do, at all times, is grab the latest sources via
CVSup and make sure you stay sync'd with that...not sure the date on the
last snapshot, but I leave it up to you to keep me in sync :)

> What is the target date for the 6.3.2 cut?   I would like to get our latest
> snapshot in that release.

	15th, but I'm being a stickler righ tnow for my problems :)



Mon, 13 Apr 1998 13:09:32 -0400 (EDT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Re: [INTERFACES] New pg_type for large object