Обсуждение: [RFC] Comments on PostPic project - Repost
I had no answer from <span class="hb"><span class="g2">pgsql-general, so I'm reposting here.</span></span><br /><br />Hello,<br/>I would like to receive comments/suggestions about this project: <a href="http://github.com/drotiro/postpic"target="_blank">http://github.com/drotiro/postpic</a>.<br /><br />In short, it'san extension that enables image processing within the database, adding a new type (image) and several functions.<br />The SQL and Java interfaces are documented on the project's wiki, so I'm not talking about these here, but instead presentsome detail on the datatype's implementation.<br /><br /> The image is represented by a struct containing some attributes(dimensions, some exif tag: shoot date, exposure time...) and a large object holding the actual image data. <br/>The idea is to have attributes stored directly to allow for efficient searching, while the large object seemed a reasonablechoice to store the possibly large image data (what are the LOBs for?).<br /> With the current large objects implementation,when a new lo is created it "lives" in the pg_largeobjects table, until someone calls lo_unlink on it. Inmy case: I create the lo on behalf of the user, then store its oid in the image's internal representation. At this point,the image can be inserted in a table, processed and so on, but when it gets deleted the corresponding lo remains dangling,unless someone or something (eg. a trigger) takes care on destroying it.<br /> Is there a way of placing some kindof hook on an object's deletion? A clean way to do a reference counting on large objects?<br />To avoid polluting pg_largeobjects,almost all of the image processing functions in PostPic return a 'temporary_image' object, which is justan alias on bytea. (Btw: I defined it using a DOMAIN. A better way?). Temporary images can be converted back to imageswhen needed via a cast (often there is a variant of the function doing this automatically).<br /><br />Thanks in advancefor your suggestions and contribution,<br /><font color="#888888">Domenico.</font>
Domenico Rotiroti wrote: > I would like to receive comments/suggestions about this > project: http://github.com/drotiro/postpic. > > In short, it's an extension that enables image processing > within the database, adding a new type (image) and several functions. > > The image is represented by a struct containing some > attributes (dimensions, some exif tag: shoot date, exposure > time...) and a large object holding the actual image data. > The idea is to have attributes stored directly to allow for > efficient searching, while the large object seemed a > reasonable choice to store the possibly large image data > (what are the LOBs for?). > With the current large objects implementation, when a new lo > is created it "lives" in the pg_largeobjects table, until > someone calls lo_unlink on it. In my case: I create the lo on > behalf of the user, then store its oid in the image's > internal representation. At this point, the image can be > inserted in a table, processed and so on, but when it gets > deleted the corresponding lo remains dangling, unless someone > or something (eg. a trigger) takes care on destroying it. > Is there a way of placing some kind of hook on an object's > deletion? A clean way to do a reference counting on large objects? If you want a system with reference counts, you'd probably have to write it yourself using triggers. There's the "vacuumlo" contrib module that removes orphaned large objects. > To avoid polluting pg_largeobjects, almost all of the image > processing functions in PostPic return a 'temporary_image' > object, which is just an alias on bytea. (Btw: I defined it > using a DOMAIN. A better way?). Temporary images can be > converted back to images when needed via a cast (often there > is a variant of the function doing this automatically). Why don't you use bytea instead of large objects in the database? That way you won't have to worry about orphaned large objects, and you don't have to convert to bytea upon retrieval. Yours, Laurenz Albe
<br /><br /><div class="gmail_quote">On Tue, Mar 16, 2010 at 1:04 PM, Albe Laurenz <span dir="ltr"><<a href="mailto:laurenz.albe@wien.gv.at">laurenz.albe@wien.gv.at</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">DomenicoRotiroti wrote:<br /> > I would like to receive comments/suggestions about this<br /> > project:<a href="http://github.com/drotiro/postpic" target="_blank">http://github.com/drotiro/postpic</a>.<br /> ><br/> > In short, it's an extension that enables image processing<br /> > within the database, adding a new type(image) and several functions.<br /> ><br /></div><div class="im">> The image is represented by a struct containingsome<br /> > attributes (dimensions, some exif tag: shoot date, exposure<br /> > time...) and a large objectholding the actual image data.<br /> > The idea is to have attributes stored directly to allow for<br /> > efficientsearching, while the large object seemed a<br /> > reasonable choice to store the possibly large image data<br/> > (what are the LOBs for?).<br /> > With the current large objects implementation, when a new lo<br /> >is created it "lives" in the pg_largeobjects table, until<br /> > someone calls lo_unlink on it. In my case: I createthe lo on<br /> > behalf of the user, then store its oid in the image's<br /> > internal representation. At thispoint, the image can be<br /> > inserted in a table, processed and so on, but when it gets<br /> > deleted thecorresponding lo remains dangling, unless someone<br /> > or something (eg. a trigger) takes care on destroying it.<br/> > Is there a way of placing some kind of hook on an object's<br /> > deletion? A clean way to do a referencecounting on large objects?<br /><br /></div>If you want a system with reference counts, you'd probably have<br />to write it yourself using triggers.<br /><br /> There's the "vacuumlo" contrib module that removes orphaned<br /><divclass="im">large objects.<br /><br /> > To avoid polluting pg_largeobjects, almost all of the image<br /> > processingfunctions in PostPic return a 'temporary_image'<br /> > object, which is just an alias on bytea. (Btw: I definedit<br /> > using a DOMAIN. A better way?). Temporary images can be<br /> > converted back to images when neededvia a cast (often there<br /> > is a variant of the function doing this automatically).<br /><br /></div>Why don'tyou use bytea instead of large objects in the database?<br /> That way you won't have to worry about orphaned largeobjects,<br /> and you don't have to convert to bytea upon retrieval.<br /><br /> Yours,<br /><font color="#888888">LaurenzAlbe<br /></font></blockquote></div><br />
Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
There's the "vacuumlo" contrib module that removes orphanedlarge objects.
Thanks for pointing out. Vacuumlo looks at oid and lo columns to find orphaned lobs, but the idea could be easily adapted/extended to looks for image columns.
Why don't you use bytea instead of large objects in the database?
That way you won't have to worry about orphaned large objects,
and you don't have to convert to bytea upon retrieval.
When I started coding PostPic I knew little about PostgreSQL's large objects implementation, and was used to more 'traditional' BLOB types found in other databases.
Regards,
Domenico
Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Thanks for pointing out. Vacuumlo looks at oid and lo columns to find orphaned lobs, but the idea could be easily adapted/extended to looks for image columns.
Regards,
Domenico
There's the "vacuumlo" contrib module that removes orphanedlarge objects.
Thanks for pointing out. Vacuumlo looks at oid and lo columns to find orphaned lobs, but the idea could be easily adapted/extended to looks for image columns.
Why don't you use bytea instead of large objects in the database?
That way you won't have to worry about orphaned large objects,
and you don't have to convert to bytea upon retrieval.
When I started coding PostPic I knew little about PostgreSQL's large objects implementation, and was used to more 'traditional' BLOB types found in other databases.
Regards,
Domenico