>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.

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

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


Mon, 24 Aug 1998 18:17:23 +0100
Florian Wunderlich <florian.wunderlich@tec.med.uni-muenchen.de>
Re: [GENERAL] DELETE statement KILL backend <AGAIN>
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



Tue, 25 Aug 1998 11:10:29 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [GENERAL] DELETE statement DOESN'T WORK ANYMORE
> 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 ?

Added to TODO.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Tue, 25 Aug 1998 13:37:40 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Re[2]: [GENERAL] DELETE statement DOESN'T WORK ANYMORE