BUG #1015: Got a signal 11 while trying to create a temp table - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1015
Logged by:          Aarjan Langereis
Email address:      A.j.langereis@chello.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 don’t know what information can be useful to you. But if you need more, please ask!

It seems to me, and please correct me if I’m 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