Thread: Postgres still dying on insert
I have tried almost everything I can think of, put postgres keepy dying.
I am running on a FreeBSD 2.2.5 system. It was complaining about not
enough shared memory. I bumped the shared mem to 16mb (the system has 128)
now it doesn't complain, it just dumps core.
The numbers are different this time, but today, the command I am trying to
execute is:
INSERT INTO word_detail VALUES (131730,18596,1)
now word_detail is:
| word_id | int4 | 4 |
| url_id | int4 | 4 |
| word_count | int2 | 2 |
and it has non-unique indexes on word_id and url_id
sc=> select count(*) from word_detail;
Field| Value
-- RECORD 0 --
count| 637466
There is quite a bit of data here as well...
sc=> INSERT INTO word_detail VALUES (131730,18596,1);
PQexec() -- Request was sent to backend, but backend closed the channel
before responding.
This probably means the backend terminated abnormally before or
while processing the request.
No debugging comes out in the log either:
FindBackend: found "/usr/local/pgsql/bin/postgres" using argv[0]
---debug info---
Quiet = f
Noversion = f
timings = f
dates = Normal
bufsize = 256
sortmem = 4096
query echo = t
DatabaseName = [sc]
----------------
InitPostgres()..
StartTransactionCommand() at Mon Apr 27 19:46:41 1998
ProcessQuery() at Mon Apr 27 19:46:41 1998
I even tried selecting this into another table and re-building the
indexes.
It allowed me to insert about 50 more values in before it started crapping
out again. Any ideas? The binaries compiled normally and passed all the
tests.
-Mike
Michael Richards wrote: > > sc=> INSERT INTO word_detail VALUES (131730,18596,1); > PQexec() -- Request was sent to backend, but backend closed the channel > before responding. > This probably means the backend terminated abnormally before or > while processing the request. Did you look in postmaster log ? Vadim
On Tue, 28 Apr 1998, Vadim B. Mikheev wrote: > > sc=> INSERT INTO word_detail VALUES (131730,18596,1); > > PQexec() -- Request was sent to backend, but backend closed the channel > > before responding. > > This probably means the backend terminated abnormally before or > > while processing the request. > > Did you look in postmaster log ? The log shows nothing... That is why I am at a loss of where to look next. I even ran it in another terminal without disassociating from the terminal. Absolutely nothing appeared on the terminal after the query started running... -Mike
On Mon, 27 Apr 1998, Michael Richards wrote:
> I have tried almost everything I can think of, put postgres keepy dying.
> I am running on a FreeBSD 2.2.5 system. It was complaining about not
> enough shared memory. I bumped the shared mem to 16mb (the system has 128)
> now it doesn't complain, it just dumps core.
> The numbers are different this time, but today, the command I am trying to
> execute is:
> INSERT INTO word_detail VALUES (131730,18596,1)
> now word_detail is:
> | word_id | int4 | 4 |
> | url_id | int4 | 4 |
> | word_count | int2 | 2 |
>
> and it has non-unique indexes on word_id and url_id
> sc=> select count(*) from word_detail;
> Field| Value
> -- RECORD 0 --
> count| 637466
>
> There is quite a bit of data here as well...
> sc=> INSERT INTO word_detail VALUES (131730,18596,1);
> PQexec() -- Request was sent to backend, but backend closed the channel
> before responding.
> This probably means the backend terminated abnormally before or
> while processing the request.
When this crash happens, do you restart postmaster itself? If so,
will that INSERT work right afterwards?
If not, what happens if you create a temporary table with the same
'schema' and insert that record? Does that work?
If not, what happens if you go to 'int8'?
>
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Hello,
I've a very similiar problem with postgres 6.3.1.
The postmaster dies with no comment on insertion on my Linux 2.0.33
96MB box, when I whant to create a new user. (I've postet the problem
with no response.)
I've track it down to an insert to pg_user.
Because of my lack of time I downgrate to 6.2.1, and all whent well.
I assume the cause is in my limited disk-place (about 30MB on the
disk where the database reside).
I have not testet 6.3.2.
Ralf
--
Fraunhofer IPK
Dipl.-Inform. Ralf Berger
Pascalstr. 8-9
10587 Berlin
Tel.: ++49-(0)30 390 06 129
Fax.: ++49-(0)30 391 10 37
---
In anything at all, perfection is finally attained not when
there is no longer anything to add, but when there is
no longer anything to take away.
Antoine de Saint Exupery
Howdy,
I just moved our existing customer database from 6.2.1 to 6.3.2. I couldn't
get the pg_dump command to work right so I made the following script.
The end result was I made over 8000 insertions in under an hour without a
failure. A random sampling of different areas shows the records made it
over intact.
I had problems with 6.3.1 but 6.3.2 seems to be ok.. My big wish list is
for access97 to seamlessly work with it....
-Rob
NOTE: This mess was a quick hack job with no commenting... took me about 5
minutes to make. IF only I could code C++ that fast... Anyway just type
datawarez.pl <filename>
where <filename> is the name of the table.
The file is generated from an earlier version of Postgres using \o
<filename> and then a select * from <filename>; at the command prompt.
This perl script does single line queries only. No Transactions.
-r
#! /usr/bin/perl
#
# PROGRAM: DATAWAREZ.PL DATE: 28-APR-98
# CREATOR: ROBERT HILTIBIDAL
#
# PURPOSE: DATAWAREZ,PL takes the data file from the command line and
# then outouts each line as a queruy to the database
# Get the filename
$file = $ARGV[0];
# Set the count variable
$count = 0;
# Start the mess
open(sql,"$file");
while (<sql>) {
if ($count == 0) {
@fields = split(/\|/,$_);
$fields[$#fields] =~ s/\n//g;
}
else {
@data = split(/\|/,$_);
$data[$#data] =~ s/\n//g;
$data[$#data] =~ s/\r//g;
$data[$#data] =~ s/\f//g;
}
$count++;
if ($count > 1) {
$query = "Insert into $file (";
$fieldcount =0;
foreach $element (@fields) {
if ($fieldcount == $#fields) {
$query .= "$element)";
}
else {
$query .= "$element,";
}
$fieldcount++;
}
$query .= " VALUES (";
$datcount = 0;
foreach $element (@data) {
if ($datcount == $#data) {
$query .= "\'$element\');";
}
else {
$query .= "\'$element\',";
}
$datcount++;
}
print "$query \n\n";
@results = `/usr/local/pgsql/bin/psql -t -A -q -d YOURDB -c "$query"`;
print @results,"\n\n";
}
}
close sql;
At 11:30 AM 4/28/98 +0200, Ralf Berger wrote:
>Hello,
>
>I've a very similiar problem with postgres 6.3.1.
>The postmaster dies with no comment on insertion on my Linux 2.0.33
>96MB box, when I whant to create a new user. (I've postet the problem
>with no response.)
>I've track it down to an insert to pg_user.
>
>Because of my lack of time I downgrate to 6.2.1, and all whent well.
>
>I assume the cause is in my limited disk-place (about 30MB on the
>disk where the database reside).
>
>I have not testet 6.3.2.
>
> Ralf
>
>--
>Fraunhofer IPK
>Dipl.-Inform. Ralf Berger
>Pascalstr. 8-9
>10587 Berlin
>
>Tel.: ++49-(0)30 390 06 129
>Fax.: ++49-(0)30 391 10 37
>
>---
>
>In anything at all, perfection is finally attained not when
>there is no longer anything to add, but when there is
>no longer anything to take away.
>
>Antoine de Saint Exupery
>--
>Official WWW Site: http://www.postgresql.org
>Online Docs & FAQ: http://www.postgresql.org/docs
>Searchable Lists: http://www.postgresql.org/mhonarc
>
>
##########################################################
Robert Hiltibidal Office 217-544-2775
Systems Programmer Fax 217-527-3550
FGInet, Inc
rob@fgi.net
morgan@springpatch.com