BUG #1015: Got a signal 11 while trying to create a temp table

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1015: Got a signal 11 while trying to create a temp table
Date: 2003-12-18 23:01:35
Message-ID: 20031218230135.8B99BCF7E8B@www.postgresql.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 1015
Logged by: Aarjan Langereis
Email address: A(dot)j(dot)langereis(at)chello(dot)nl
PostgreSQL version: 7.3.4
Operating system: RedHat Linux 9 kernel 2.4.20-20.9
Description: Got a signal 11 while trying to create a temp table
Details:

I tried to create a temp table and got my back-end restarting because of a signal 11.

The 3 tables involved:

CREATE TABLE hosts (
hostID serial primary key,
hostip cidr NOT NULL,
hostname varchar(50),
lastseen timestamp without time zone default ('1970-01-01 01:00'),
total integer default 0,
image varchar(20) default 'hosts/unknown.png'
);
CREATE TABLE cpus (
cpuID integer primary key,
cpuname varchar(20),
lastseen timestamp without time zone default ('1970-01-01 01:00'),
total integer default 0,
image varchar(20) default 'cpus/unknown.png'
);
CREATE TABLE blocks (
blockID varchar(30) primary key,
blockdate timestamp without time zone NOT NULL,
hostID integer REFERENCES hosts,
orgIP cidr NOT NULL,
email varchar(30) NOT NULL,
osID integer NOT NULL,
cpuID integer NOT NULL,
version integer NOT NULL,
core integer NOT NULL,
amount integer NOT NULL
);

Hosts has 205 rows
Cpus has 17 rows
And blocks has 3194409 rows

This is the problem query:

Create TEMP table tmphosts AS
select hosts.hostid, hosts.hostip, hosts.hostname, max(blockdate) as lastseen, sum(amount) as total, hosts.image
from hosts left join blocks on hosts.hostid=blocks.hostid
group by hosts.hostid, hosts.hostip, hosts.hostname, hosts.image;

But even without the first line is does not work. However this query does work properly:

Create TEMP table tmpcpus AS
select cpus.cpuid, cpuname, max(blockdate) as lastseen, sum(amount) as total, image
from cpus left join blocks on cpus.cpuid=blocks.cpuid
group by cpus.cpuid, cpuname, image;
They look rather the same to me But with the first one I got this error in psql:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

In the log was this:

LOG: server process (pid 27196) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
FATAL: The database system is starting up
LOG: database system was interrupted at 2003-12-18 19:16:21 CET
LOG: checkpoint record is at 6/9312CD40
LOG: redo record is at 6/9312CD40; undo record is at 0/0; shutdown FALSE
LOG: next transaction id: 28888909; next oid: 15667926
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 6/9312CD80
LOG: ReadRecord: unexpected pageaddr 6/8B162000 in log file 6, segment 147, offset 1449984
LOG: redo done at 6/9315EE4C
LOG: database system is ready

I dont know what information can be useful to you. But if you need more, please ask!

It seems to me, and please correct me if Im wrong, that there is a limit to the size that a join can handle.

I hope that the information provided is of any use to you.

Yours,

Aarjan

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Russell Garrett 2003-12-18 23:35:27 Re: Urgent: Key constraints behaving weirdly
Previous Message Tom Lane 2003-12-18 22:51:12 Re: libpq3 + ssl memory leak