Обсуждение: [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 />