From: | Sferacarta Software <sferac(at)bo(dot)nettuno(dot)it> |
---|---|
To: | "David Ben-Yaacov" <David_BenYaacov(at)itd(dot)sterling(dot)com>, Florian Wunderlich <florian(dot)wunderlich(at)tec(dot)med(dot)uni-muenchen(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)PostgresSQL(dot)org |
Subject: | Re[2]: [GENERAL] DELETE statement DOESN'T WORK ANYMORE |
Date: | 1998-08-25 09:10:29 |
Message-ID: | 0465.980825@bo.nettuno.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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(at)bo(dot)nettuno(dot)it
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Good | 1998-08-25 12:13:34 | Re: [GENERAL] DBD::Pg (Errata...) |
Previous Message | claudio navarro | 1998-08-25 07:14:58 | TRANSACTION ISOLATION |
From | Date | Subject | |
---|---|---|---|
Next Message | Tatsuo Ishii | 1998-08-25 09:59:54 | vacuum problem |
Previous Message | Andreas Zeugswetter | 1998-08-25 08:55:11 | Portability bug in genbki.sh --> initdb hangup |