Re: Image File System Question - Mailing list pgsql-general

vishal saberwal wrote:
> hi,
>
> My server is Postgres 8.0.1 on fedora core2.
> My clients are remote and interface with my server using .NET GUI.
>
> We are trying to store many images/icons/audio/video clips in our system.
> We expect many of these. The way we are doing it is using Hierarchical
> File System.

HFS is an old Apple Macintosh filesystem - an unlikely choice. You'll
probably find it's ext3.

> I understand we need to limit the size of these directories by
> controlling number of resources in each directory.

Less important nowadays than it used to be, but usually done via hashing
the filename. So, file 123456.gif is stored in 1/2/3/123456.gif

> The way i want to let anone access these resources is only through
> stored procedures rather than direct downloading.

Why?
What are the benefits of this system?

> I am trying to create stored procedure/function API for the same. These
> functions will check for the permissions for the user and/or the file,
> check the location from the schema and then would "stream" it out for
> the GUI to use.

Why not just set up a webserver and get it to authenticate to your database?

> The question is:
> (1) How do i use the database stored procedures/functions as a tunnel
> for just streaming the data rather than storing it in database?
> That is, a function that given a Image ID for example, will read the
> location from the table and then just go to the location and stream out
> the bits.
> (2) Am i right in saying that it can't solely be done iwth plpgsql but
> would need somem c/c++ api.

Any of the "untrusted" languages (which of course includes "C"). File
access will be different in each of course - pick whichever you are most
familiar with. The key differences between a "trusted" and "untrusted"
version of a language are:
1. Untrusted languages can access the rest of the system
2. Functions in untrusted languages can only be added by a superuser.

> (3) Has anyone here done something like this and can share with me how
> he/she implemented this.
>
> I did do my homework of googling for something like this but may be my
> search skills were not strong enough to find some substantial
> information/HOW TOs or examples.

Well, there is the "procedural languages" section of the manuals. It
might also be worth checking on pgfoundry to see if there is anything
useful there.

--
   Richard Huxton
   Archonet Ltd