Thread

1. New pg_type for large object

   David Hartwig <daveh@insightdist.com> — 1998-04-09T22:54:24Z

   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!
   
   

2. Re: [HACKERS] New pg_type for large object

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-04-10T01:18:24Z

   > 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
   
   

3. Re: [HACKERS] New pg_type for large object

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-04-10T02:10:34Z

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

4. Re: [INTERFACES] New pg_type for large object

   Peter T Mount <pgint@maidast.demon.co.uk> — 1998-04-10T10:06:34Z

   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
   
   
   

5. Re: [HACKERS] New pg_type for large object

   Kent S. Gordon <kgor@inetspace.com> — 1998-04-10T15:32:38Z

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

6. Re: [HACKERS] New pg_type for large object

   Peter T Mount <psqlhack@maidast.demon.co.uk> — 1998-04-10T18:48:29Z

   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
   
   
   

7. Re: [HACKERS] Re: [INTERFACES] New pg_type for large object

   David Hartwig <daveh@insightdist.com> — 1998-04-13T14:29:17Z

   
   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.
   
   

8. Re: [HACKERS] Re: [INTERFACES] New pg_type for large object

   Marc G. Fournier <scrappy@hub.org> — 1998-04-13T15:05:53Z

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

9. Re: [HACKERS] Re: [INTERFACES] New pg_type for large object

   David Hartwig <daveh@insightdist.com> — 1998-04-13T16:10:58Z

   
   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.
   
   
   
   

10. Re: [HACKERS] Re: [INTERFACES] New pg_type for large object

    Marc G. Fournier <scrappy@hub.org> — 1998-04-13T17:09:32Z

    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 :)