Thread: first post, need info on postgres + storing objects (getting rid of them mainly)

hi,

if this is the wrong place to post, please let me know.

I am trying to fix a mess on a system running a postgresql database (7.1 I
think)
java objects where stored on this server but it seems to me that when the
rows
holding the OID's of these objcets are deleted, or the tables holding the
multiple
rows is dropped, the attached objects aren't removed themselves.

(I have double checked this assumptionof mine just now)
for each OID  with number yyyyyyyyyyy   there is a file in the datadir named
xinxyyyyyyyyyyy (header?) and a (value?) file of xinvyyyyyyyyyyy

both files are not removed after dropping the rows linking to these files.
am I doing something wrong here, or is this a 'feature'? Does anyone have
similar problems?

I tried the vacuum function which only sizes down some system tables but
leaves
the object files untouched. Is there another function I should be looking
into?

for info: I have 400.000 files in the datadir by now and still growing.
------------
and now for my question, the above is just a cry for help and you can ignore
it unless
you actually have 'the' answer for me. (in which case you have my gratitude)

Q: is it possible to delete files from the database directory (xinx11111 and
xinv11111 files)
without the rest of the system becoming unstable?
(ofcourse I mean only those files that are not referenced by the database at
that point)
Are the files on disk referenced from any system tables and if so will the
disappearing of
these files cause serious grief for me and our customer?


I really hope you can help me.
regards,
joris hilhorst

On Thu, 14 Feb 2002, Joris Hilhorst wrote:

> if this is the wrong place to post, please let me know.
>
> I am trying to fix a mess on a system running a postgresql database (7.1 I
> think)
> java objects where stored on this server but it seems to me that when the
> rows
> holding the OID's of these objcets are deleted, or the tables holding the
> multiple
> rows is dropped, the attached objects aren't removed themselves.
>
> (I have double checked this assumptionof mine just now)
> for each OID  with number yyyyyyyyyyy   there is a file in the datadir named
> xinxyyyyyyyyyyy (header?) and a (value?) file of xinvyyyyyyyyyyy
>
> both files are not removed after dropping the rows linking to these files.
> am I doing something wrong here, or is this a 'feature'? Does anyone have
> similar problems?

Since AFAIK you can have multiple rows pointing to one object, and I could
see cases where someone would want to get the lo oid, delete a row and
insert a new one that points to that same lo rather than having to import
the lo again, it's hard to say.  If you're not in one of those cases a
trigger on the table to do the cleanup is probably a good idea.

> I tried the vacuum function which only sizes down some system tables but
> leaves
> the object files untouched. Is there another function I should be looking
> into?

You might want to look at contrib/vacuumlo which looks for orphaned large
objects.