Re[2]: [GENERAL] DELETE statement DOESN'T WORK ANYMORE

Hello Florian,

Thanks for your reply.

lunedì, 24 agosto 98, you wrote:

>>In my experience, the problem seems to be caused by a lot of data being put
>>into the database.  We are using the database to ingest real-time data 24
>>hours a day/7 days a week. The data comes in about every three minutes.
>>While I was not able to identify what the exact cause has been, I have
>>noticed that before the problem becomes critical (before the backend
>>terminates abnormally), the (number of) indexes do not correspond to the
>>actual table.  That leads me to believe that the indexes do not get created
>>on all occasions.  After some time, the table's internal indexes may be come
>>corrupted, and vacuuming does no good.
>>
>>When trying to fix this, I first delete the index, then recreate it., then
>>vacuum.  If that doesn't work, then I drop the table, create the index,
>>reload the data, and then vacuum the table.
>>
>>I would be curious to see if anyone else has had this type of problem and
>>what their solutions were.

FW> Same with us here, we use a really big database, but in my experience, it
FW> happend only when I killed (with SIGTERM, but anyway) a single postgres
FW> process that made an "endless" query. I think that some internal tables are
FW> left over in the data/base directory tree, and postgres tends to get
FW> confused about them.
FW> Not sure about this anyway.

I want to say something about this stuff to hackers;
I see that temporary files are created in the same directory of data
and this generate a few of confusion, often I find files like
pg_psort# or pg_vlock and I'm not sure if I can safety remove them or not.
May I suggest to have a tmp directory on data/base/dbname/tmp
directory tree to these files ?

FW> DROP/CREATE INDEX didn't solve this, I always did a DROP DATABASE and a
FW> complete reload of all data, and then it worked fine again.

Your is a good tip Florian but I think it's not very professional, I
can't imagine me removing and re-creating the database every time I
have a problem with it. I can't believe that PostgreSQL is like
M$-Windows that I have to re-install every time I have a problem.

Anyway DROP DATABASE statement doesn't work for me, the postmaster gives me the same
error message before die.
Thanks anyway for your tips Florian.

Best regards,
   Jose'                            mailto:sferac@bo.nettuno.it