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

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

In response to

Responses

Browse pgsql-general by date

  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

Browse pgsql-hackers by date

  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