Thread: New pg_type for large object
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!
Attachment
> 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
> > > 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)
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
>>>>> "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
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
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.
Attachment
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...
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.
Attachment
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 :)