DELETE statement KILL backend

Hello all,

I have a strange problem that I can't understand.
I have been my PostgreSQL-6.3.2 on Linux-ELF-2.0.34 working without any problem for many
months. This morning I can't DELETE anymore.
If I try to delete a row from any table I have this message:

PGexec() -- Request has sent to backend, but backend closed the
channel before responding.
This probably means the backend terminated abnormally before or while
processing the request.

and then the backend dies.

I tried every statement as:
INSERT
UPDATE
SELECT
DROP TABLE
and all of them works but DELETE,

I tried also VACUUM without success.

PLEASE help me !!

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

I've had this problem a number of times. Before I tell you what I did to
solve this problem, let me warn you that I am a novice at DBs (PostgreSQL is
my first database).

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.

I can provide the scripts to check the database to the newsgroup or to you
directly, if desired.

David

-----Original Message-----
From: Sferacarta Software <sferac@bo.nettuno.it>
To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
Date: Friday, August 21, 1998 10:57 AM
Subject: [GENERAL] DELETE statement KILL backend <AGAIN>

Hello all,

I have a strange problem that I can't understand.
I have been my PostgreSQL-6.3.2 on Linux-ELF-2.0.34 working without any

problem for many

Hello David,

Thanks to reply to my answer.

DBY> I've had this problem a number of times. Before I tell you what I did to
DBY> solve this problem, let me warn you that I am a novice at DBs (PostgreSQL is
DBY> my first database).

DBY> In my experience, the problem seems to be caused by a lot of data being put
DBY> into the database.
Unfortunately this isn't my case.
DBY> We are using the database to ingest real-time data 24
DBY> hours a day/7 days a week. The data comes in about every three minutes.
DBY> While I was not able to identify what the exact cause has been, I have
DBY> noticed that before the problem becomes critical (before the backend
DBY> terminates abnormally), the (number of) indexes do not correspond to the
DBY> actual table. That leads me to believe that the indexes do not get created
DBY> on all occasions. After some time, the table's internal indexes may be come
DBY> corrupted, and vacuuming does no good.
I have this problem on every table, those with index and those
without.

Right now, if I create a table without indexes and then I "DELETE FROM
table" the backend dies immediately.

DBY> When trying to fix this, I first delete the index, then recreate it., then
DBY> vacuum. If that doesn't work, then I drop the table, create the index,
DBY> reload the data, and then vacuum the table.

DBY> I would be curious to see if anyone else has had this type of problem and
DBY> what their solutions were.
Me too.

DBY> I can provide the scripts to check the database to the newsgroup or to you
DBY> directly, if desired.
I'm not sure if this can solve my problem, anyway send me it.
Thanks for all,
Jose'

DBY> -----Original Message-----
DBY> From: Sferacarta Software <sferac@bo.nettuno.it>
DBY> To: pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org>
DBY> Date: Friday, August 21, 1998 10:57 AM
DBY> Subject: [GENERAL] DELETE statement KILL backend <AGAIN>

Hello all,

I have a strange problem that I can't understand.
I have been my PostgreSQL-6.3.2 on Linux-ELF-2.0.34 working without any

DBY> problem for many

months. This morning I can't DELETE anymore.
If I try to delete a row from any table I have this message:

PGexec() -- Request has sent to backend, but backend closed the
channel before responding.
This probably means the backend terminated abnormally before or while
processing the request.

and then the backend dies.

I tried every statement as:
INSERT
UPDATE
SELECT
DROP TABLE
and all of them works but DELETE,

I tried also VACUUM without success.

PLEASE help me !!

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

Best regards,
Sferacarta mailto:sferac@bo.nettuno.it

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.

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

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)