Thread: Synchronize filenames in table with filesystem

Synchronize filenames in table with filesystem

From
Ludwig Kniprath
Date:
Hi List,
not another question on how to store files (db or filesystem), i decided to use the filesystem.

I'm now searching for a trigger, that deletes the physical file when deleting a database-record containing the filename
inone of its fields. Is there a sample somewhere how this could be done? I'm runnig PG 8.4 on a windows machine. 

Regards
Ludwig

Re: Synchronize filenames in table with filesystem

From
Richard Broersma
Date:
On Tue, Dec 1, 2009 at 9:19 AM, Ludwig Kniprath <ludwig@kni-online.de> wrote:
> Hi List,
> not another question on how to store files (db or filesystem), i decided to use the filesystem.
>
> I'm now searching for a trigger, that deletes the physical file when deleting a database-record containing the
filenamein one of its fields. Is there a sample somewhere how this could be done? I'm runnig PG 8.4 on a windows
machine.


Perhaps when you delete your record, you can use a before delete
trigger to insert this path to a "Files_To_Delete" table.  Then using
Cron, you can query this table to get the list of file you want to
delete.

On the other hand, if you really want to delete each file with a
trigger, you are probably better off using Large Objects (lo):
http://www.postgresql.org/docs/8.4/interactive/lo.html

And in the off chance that you one day use the truncate command you
can periodically schedule the vacuumlo command:
http://www.postgresql.org/docs/8.4/interactive/vacuumlo.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: Synchronize filenames in table with filesystem

From
Steve Atkins
Date:
On Dec 1, 2009, at 9:19 AM, Ludwig Kniprath wrote:

> Hi List,
> not another question on how to store files (db or filesystem), i decided to use the filesystem.
>
> I'm now searching for a trigger, that deletes the physical file when deleting a database-record containing the
filenamein one of its fields. Is there a sample somewhere how this could be done? I'm runnig PG 8.4 on a windows
machine.

I've done that by having the trigger put the name of the file to be deleted in a "to be deleted" table. Then an
externalprocess polls that table and deletes any file it finds in there (using listen/notify if you need that to happen
immediately,but just polling works fine if it's just garbage collection). 

That has the advantage of not deleting files until the transaction commits too.

Cheers,
  Steve


Re: Synchronize filenames in table with filesystem

From
"Bret"
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Atkins
> Sent: Tuesday, December 01, 2009 9:44 AM
> To: pgsql-general General
> Subject: Re: [GENERAL] Synchronize filenames in table with filesystem
>
>
> On Dec 1, 2009, at 9:19 AM, Ludwig Kniprath wrote:
>
> > Hi List,
> > not another question on how to store files (db or
> filesystem), i decided to use the filesystem.
> >
> > I'm now searching for a trigger, that deletes the physical
> file when deleting a database-record containing the filename
> in one of its fields. Is there a sample somewhere how this
> could be done? I'm runnig PG 8.4 on a windows machine.
>
> I've done that by having the trigger put the name of the file
> to be deleted in a "to be deleted" table. Then an external
> process polls that table and deletes any file it finds in
> there (using listen/notify if you need that to happen
> immediately, but just polling works fine if it's just garbage
> collection).
>
> That has the advantage of not deleting files until the
> transaction commits too.
>
> Cheers,
>   Steve
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

I'm with Ludwig..

Better to have the database perform it's primary function, and stay away
from os chores.




Re: Synchronize filenames in table with filesystem

From
silly8888
Date:
You can also use LISTEN/NOTIFY.



On Tue, Dec 1, 2009 at 6:25 PM, Bret <bret_stern@machinemanagement.com> wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org
>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Steve Atkins
>> Sent: Tuesday, December 01, 2009 9:44 AM
>> To: pgsql-general General
>> Subject: Re: [GENERAL] Synchronize filenames in table with filesystem
>>
>>
>> On Dec 1, 2009, at 9:19 AM, Ludwig Kniprath wrote:
>>
>> > Hi List,
>> > not another question on how to store files (db or
>> filesystem), i decided to use the filesystem.
>> >
>> > I'm now searching for a trigger, that deletes the physical
>> file when deleting a database-record containing the filename
>> in one of its fields. Is there a sample somewhere how this
>> could be done? I'm runnig PG 8.4 on a windows machine.
>>
>> I've done that by having the trigger put the name of the file
>> to be deleted in a "to be deleted" table. Then an external
>> process polls that table and deletes any file it finds in
>> there (using listen/notify if you need that to happen
>> immediately, but just polling works fine if it's just garbage
>> collection).
>>
>> That has the advantage of not deleting files until the
>> transaction commits too.
>>
>> Cheers,
>>   Steve
>>
>>
>> --
>> Sent via pgsql-general mailing list
>> (pgsql-general@postgresql.org) To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> I'm with Ludwig..
>
> Better to have the database perform it's primary function, and stay away
> from os chores.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>