Thread: BUG #8025: PostgreSQL crash (>= 9.1 64 bit)

The following bug has been logged on the website:

Bug reference:      8025
Logged by:          lindebg
Email address:      lindebg@gmail.com
PostgreSQL version: 9.2.3
Operating system:   Linux 64 bit (Debian, Gentoo), Windows 8 64 bit
Description:        =


Happened to me a failure of PostgreSQL in production use.
I would like to report a minimum combination of SQL, which crashes
PostgreSQL 64 bit.
Tested versions of PostgreSQL:
- Linux 64 bit: 9.2.3, 9.1.8, 9.3.devel (problem)
- Linux 32 bit: 9.2.3 (ok)
- Linux 64 bit: 9.0.12 (ok)
- Windows 64 bit: 9.2.3 (problem)


The combination of SQL:


create table states
(
    param varchar primary key,
    state varchar not null
);
insert into states(param,state) select 'status_interval','3 hours';
insert into states(param,state) select 'last_status','0';

create table machines
(
    machineid serial primary key,
    machinename varchar not null
);

create table progress
(
    machineid serial primary key,
    keyname varchar not null,
    startdate timestamp not null,
    lastdate timestamp,
    lastenddate timestamp,
    currentprogress float not null,
    maxprogress float not null,
    lastmaxprogress float,
    currentdate timestamp not null,
    message varchar,
    remainingseconds int not null,
    importcomplete boolean not null,
    test varchar,
    test1 int,
    test2 int,
    test3 int,
    test4 varchar,
    test5 varchar
);

create or replace function getstatusinterval()
returns interval
language plpgsql
as
$$
begin
    return (select state from states where param=3D'status_interval')::interva=
l;
end
$$;

create or replace function getlaststatus()
returns int
as
$$
begin
    return (select state from states where param=3D'last_status')::int;
end
$$
language plpgsql;

create or replace view vprogress
as
select
    p.machineid,
    p.keyname,
    p.startdate,
    p.lastdate,
    p.lastenddate,
    p.currentprogress,
    p.maxprogress,
    p.lastmaxprogress,
    p.currentdate,
    p.message,
    (p.currentprogress*100)::int as percent,
    getstatusinterval() as statusinterval,
    p.remainingseconds,
    p.importcomplete,
    p.test
from progress p;

create or replace view vinfo
as =

select
    m.machineid,
    (select p.lastenddate from vprogress p where p.machineid =3D m.machineid) =
as
lastenddate,
    getlaststatus() as laststatus
from machines m
;

-- ----------------------------------------

select * from vinfo; -- ok

-- Modified at a later time ---------------

create or replace view vprogress
as
select
    p.machineid,
    p.keyname,
    p.startdate,
    p.lastdate,
    p.lastenddate,
    p.currentprogress,
    p.maxprogress,
    p.lastmaxprogress,
    p.currentdate,
    p.message,
    (p.currentprogress*100)::int as percent,
    getstatusinterval() as statusinterval,
    p.remainingseconds,
    p.importcomplete,
    p.test,
    p.test1,
    p.test2,
    p.test3,
    p.test4,
    p.test5
from progress p;

-- ----------------------------------------

select * from vinfo; -- crash.



Logs - SQL: select * from vinfo:


2013-03-31 23:06:36.095 CEST 24631 LOG:  database system was shut down at
2013-03-31 23:06:30 CEST
2013-03-31 23:06:36.099 CEST 24635 LOG:  autovacuum launcher started
2013-03-31 23:06:36.099 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:06:36.581 CEST 24637 LOG:  incomplete startup packet
2013-03-31 23:07:13.735 CEST 24630 LOG:  server process (PID 24653) was
terminated by signal 11: Segmentation fault
2013-03-31 23:07:13.735 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo; -- crash
    =

2013-03-31 23:07:13.736 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:07:13.736 CEST 24652 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:07:13.736 CEST 24652 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:07:13.736 CEST 24652 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:07:13.736 CEST 24635 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:07:13.736 CEST 24635 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:07:13.736 CEST 24635 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:07:13.737 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:07:13.786 CEST 24655 LOG:  database system was interrupted;
last known up at 2013-03-31 23:06:36 CEST
2013-03-31 23:07:13.786 CEST 24655 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:07:13.801 CEST 24655 LOG:  redo starts at 0/1764560
2013-03-31 23:07:13.804 CEST 24655 LOG:  record with zero length at
0/17A9190
2013-03-31 23:07:13.804 CEST 24655 LOG:  redo done at 0/17A9160
2013-03-31 23:07:13.804 CEST 24655 LOG:  last completed transaction was at
log time 2013-03-31 23:07:07.094992+02
2013-03-31 23:07:13.890 CEST 24659 LOG:  autovacuum launcher started
2013-03-31 23:07:13.890 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.095 CEST 24630 LOG:  server process (PID 24668) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.095 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.095 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.096 CEST 24659 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.096 CEST 24659 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.096 CEST 24659 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.097 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.107 CEST 24670 LOG:  database system was interrupted;
last known up at 2013-03-31 23:07:13 CEST
2013-03-31 23:08:33.108 CEST 24670 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.110 CEST 24670 LOG:  redo starts at 0/17A91F0
2013-03-31 23:08:33.111 CEST 24670 LOG:  record with zero length at
0/17B0ED8
2013-03-31 23:08:33.111 CEST 24670 LOG:  redo done at 0/17AF070
2013-03-31 23:08:33.122 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.122 CEST 24674 LOG:  autovacuum launcher started
2013-03-31 23:08:33.136 CEST 24630 LOG:  server process (PID 24676) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.136 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.136 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.136 CEST 24674 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.136 CEST 24674 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.136 CEST 24674 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.137 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.144 CEST 24678 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.144 CEST 24678 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.150 CEST 24678 LOG:  record with zero length at
0/17B0F38
2013-03-31 23:08:33.150 CEST 24678 LOG:  redo is not required
2013-03-31 23:08:33.165 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.165 CEST 24682 LOG:  autovacuum launcher started
2013-03-31 23:08:33.177 CEST 24630 LOG:  server process (PID 24684) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.177 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.177 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.177 CEST 24682 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.177 CEST 24682 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.177 CEST 24682 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.177 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.191 CEST 24686 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.191 CEST 24686 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.193 CEST 24686 LOG:  record with zero length at
0/17B0F98
2013-03-31 23:08:33.193 CEST 24686 LOG:  redo is not required
2013-03-31 23:08:33.201 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.201 CEST 24690 LOG:  autovacuum launcher started
2013-03-31 23:08:33.218 CEST 24630 LOG:  server process (PID 24692) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.218 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.218 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.218 CEST 24690 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.219 CEST 24690 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.219 CEST 24690 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.219 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.225 CEST 24694 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.225 CEST 24694 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.238 CEST 24694 LOG:  record with zero length at
0/17B0FF8
2013-03-31 23:08:33.238 CEST 24694 LOG:  redo is not required
2013-03-31 23:08:33.245 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.245 CEST 24698 LOG:  autovacuum launcher started
2013-03-31 23:08:33.263 CEST 24630 LOG:  server process (PID 24700) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.263 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.263 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.264 CEST 24698 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.264 CEST 24698 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.264 CEST 24698 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.264 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.270 CEST 24702 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.270 CEST 24702 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.276 CEST 24702 LOG:  record with zero length at
0/17B1058
2013-03-31 23:08:33.276 CEST 24702 LOG:  redo is not required
2013-03-31 23:08:33.292 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.292 CEST 24706 LOG:  autovacuum launcher started
2013-03-31 23:08:33.304 CEST 24630 LOG:  server process (PID 24708) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.304 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.304 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.304 CEST 24706 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.304 CEST 24706 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.304 CEST 24706 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.305 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.319 CEST 24710 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.319 CEST 24710 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.324 CEST 24710 LOG:  record with zero length at
0/17B10B8
2013-03-31 23:08:33.324 CEST 24710 LOG:  redo is not required
2013-03-31 23:08:33.338 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.338 CEST 24714 LOG:  autovacuum launcher started
2013-03-31 23:08:33.347 CEST 24630 LOG:  server process (PID 24716) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.347 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.347 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.348 CEST 24714 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.348 CEST 24714 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.348 CEST 24714 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.348 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.365 CEST 24718 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.366 CEST 24718 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.368 CEST 24718 LOG:  record with zero length at
0/17B1118
2013-03-31 23:08:33.368 CEST 24718 LOG:  redo is not required
2013-03-31 23:08:33.378 CEST 24722 LOG:  autovacuum launcher started
2013-03-31 23:08:33.378 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.384 CEST 24630 LOG:  server process (PID 24724) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.384 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.384 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.384 CEST 24722 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.384 CEST 24722 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.384 CEST 24722 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.384 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.396 CEST 24726 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.397 CEST 24726 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.402 CEST 24726 LOG:  record with zero length at
0/17B1178
2013-03-31 23:08:33.402 CEST 24726 LOG:  redo is not required
2013-03-31 23:08:33.409 CEST 24730 LOG:  autovacuum launcher started
2013-03-31 23:08:33.409 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.427 CEST 24630 LOG:  server process (PID 24732) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.427 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.427 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.427 CEST 24730 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.427 CEST 24730 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.427 CEST 24730 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.427 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.438 CEST 24734 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.438 CEST 24734 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.454 CEST 24734 LOG:  record with zero length at
0/17B11D8
2013-03-31 23:08:33.454 CEST 24734 LOG:  redo is not required
2013-03-31 23:08:33.459 CEST 24735 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.460 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.461 CEST 24740 LOG:  autovacuum launcher started
2013-03-31 23:08:33.494 CEST 24630 LOG:  server process (PID 24742) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.494 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.494 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.494 CEST 24740 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.494 CEST 24740 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.494 CEST 24740 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.495 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.504 CEST 24744 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.504 CEST 24744 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.527 CEST 24744 LOG:  record with zero length at
0/17B1238
2013-03-31 23:08:33.527 CEST 24744 LOG:  redo is not required
2013-03-31 23:08:33.534 CEST 24745 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.538 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.538 CEST 24750 LOG:  autovacuum launcher started
2013-03-31 23:08:33.570 CEST 24630 LOG:  server process (PID 24752) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.570 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.570 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.570 CEST 24750 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.570 CEST 24750 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.570 CEST 24750 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.571 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.579 CEST 24754 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.579 CEST 24754 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.583 CEST 24754 LOG:  record with zero length at
0/17B1298
2013-03-31 23:08:33.584 CEST 24754 LOG:  redo is not required
2013-03-31 23:08:33.593 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.593 CEST 24758 LOG:  autovacuum launcher started
2013-03-31 23:08:33.618 CEST 24630 LOG:  server process (PID 24760) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.618 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.618 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.618 CEST 24758 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.618 CEST 24758 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.618 CEST 24758 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.618 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.626 CEST 24762 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.626 CEST 24762 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.631 CEST 24762 LOG:  record with zero length at
0/17B12F8
2013-03-31 23:08:33.631 CEST 24762 LOG:  redo is not required
2013-03-31 23:08:33.646 CEST 24766 LOG:  autovacuum launcher started
2013-03-31 23:08:33.646 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.659 CEST 24630 LOG:  server process (PID 24768) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.659 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.660 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.660 CEST 24766 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.660 CEST 24766 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.660 CEST 24766 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.660 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.671 CEST 24770 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.671 CEST 24770 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.675 CEST 24770 LOG:  record with zero length at
0/17B1358
2013-03-31 23:08:33.675 CEST 24770 LOG:  redo is not required
2013-03-31 23:08:33.694 CEST 24771 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.723 CEST 24773 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.751 CEST 24775 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.782 CEST 24777 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.799 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.799 CEST 24782 LOG:  autovacuum launcher started
2013-03-31 23:08:33.819 CEST 24630 LOG:  server process (PID 24784) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.819 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.819 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.819 CEST 24782 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.819 CEST 24782 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.819 CEST 24782 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.820 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.898 CEST 24786 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.898 CEST 24787 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:33.898 CEST 24786 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.902 CEST 24786 LOG:  record with zero length at
0/17B13B8
2013-03-31 23:08:33.902 CEST 24786 LOG:  redo is not required
2013-03-31 23:08:33.916 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.916 CEST 24792 LOG:  autovacuum launcher started
2013-03-31 23:08:33.947 CEST 24630 LOG:  server process (PID 24794) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.947 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.947 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.947 CEST 24792 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.947 CEST 24792 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.947 CEST 24792 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.948 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.957 CEST 24796 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.957 CEST 24796 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:33.962 CEST 24796 LOG:  record with zero length at
0/17B1418
2013-03-31 23:08:33.962 CEST 24796 LOG:  redo is not required
2013-03-31 23:08:33.971 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:33.971 CEST 24800 LOG:  autovacuum launcher started
2013-03-31 23:08:33.987 CEST 24630 LOG:  server process (PID 24802) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:33.987 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:33.987 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:33.988 CEST 24800 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:33.988 CEST 24800 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:33.988 CEST 24800 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:33.989 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:33.997 CEST 24804 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:33 CEST
2013-03-31 23:08:33.997 CEST 24804 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.002 CEST 24804 LOG:  record with zero length at
0/17B1478
2013-03-31 23:08:34.002 CEST 24804 LOG:  redo is not required
2013-03-31 23:08:34.008 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.008 CEST 24808 LOG:  autovacuum launcher started
2013-03-31 23:08:34.029 CEST 24630 LOG:  server process (PID 24810) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.029 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.029 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.029 CEST 24808 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.029 CEST 24808 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.029 CEST 24808 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.030 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.038 CEST 24812 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.038 CEST 24812 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.043 CEST 24812 LOG:  record with zero length at
0/17B14D8
2013-03-31 23:08:34.043 CEST 24812 LOG:  redo is not required
2013-03-31 23:08:34.057 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.057 CEST 24816 LOG:  autovacuum launcher started
2013-03-31 23:08:34.072 CEST 24630 LOG:  server process (PID 24818) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.072 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.072 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.072 CEST 24816 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.072 CEST 24816 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.072 CEST 24816 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.073 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.078 CEST 24820 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.079 CEST 24820 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.086 CEST 24820 LOG:  record with zero length at
0/17B1538
2013-03-31 23:08:34.086 CEST 24820 LOG:  redo is not required
2013-03-31 23:08:34.098 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.098 CEST 24824 LOG:  autovacuum launcher started
2013-03-31 23:08:34.112 CEST 24630 LOG:  server process (PID 24826) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.112 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.112 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.113 CEST 24824 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.113 CEST 24824 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.113 CEST 24824 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.113 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.121 CEST 24828 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.121 CEST 24828 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.127 CEST 24828 LOG:  record with zero length at
0/17B1598
2013-03-31 23:08:34.127 CEST 24828 LOG:  redo is not required
2013-03-31 23:08:34.135 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.135 CEST 24832 LOG:  autovacuum launcher started
2013-03-31 23:08:34.151 CEST 24630 LOG:  server process (PID 24834) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.151 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.151 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.151 CEST 24832 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.151 CEST 24832 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.151 CEST 24832 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.152 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.158 CEST 24836 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.158 CEST 24836 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.166 CEST 24836 LOG:  record with zero length at
0/17B15F8
2013-03-31 23:08:34.166 CEST 24836 LOG:  redo is not required
2013-03-31 23:08:34.174 CEST 24840 LOG:  autovacuum launcher started
2013-03-31 23:08:34.174 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.193 CEST 24630 LOG:  server process (PID 24842) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.193 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.194 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.194 CEST 24840 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.194 CEST 24840 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.194 CEST 24840 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.199 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.204 CEST 24844 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.204 CEST 24844 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.207 CEST 24844 LOG:  record with zero length at
0/17B1658
2013-03-31 23:08:34.207 CEST 24844 LOG:  redo is not required
2013-03-31 23:08:34.214 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.214 CEST 24848 LOG:  autovacuum launcher started
2013-03-31 23:08:34.233 CEST 24630 LOG:  server process (PID 24850) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.233 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.233 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.233 CEST 24848 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.233 CEST 24848 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.233 CEST 24848 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.234 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.241 CEST 24852 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.241 CEST 24852 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.245 CEST 24852 LOG:  record with zero length at
0/17B16B8
2013-03-31 23:08:34.245 CEST 24852 LOG:  redo is not required
2013-03-31 23:08:34.252 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.252 CEST 24856 LOG:  autovacuum launcher started
2013-03-31 23:08:34.273 CEST 24630 LOG:  server process (PID 24858) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.273 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.274 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.274 CEST 24856 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.274 CEST 24856 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.274 CEST 24856 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.274 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.284 CEST 24860 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.284 CEST 24860 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.287 CEST 24860 LOG:  record with zero length at
0/17B1718
2013-03-31 23:08:34.287 CEST 24860 LOG:  redo is not required
2013-03-31 23:08:34.295 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.295 CEST 24864 LOG:  autovacuum launcher started
2013-03-31 23:08:34.314 CEST 24630 LOG:  server process (PID 24866) was
terminated by signal 11: Segmentation fault
2013-03-31 23:08:34.314 CEST 24630 DETAIL:  Failed process was running:
select * from vinfo
2013-03-31 23:08:34.314 CEST 24630 LOG:  terminating any other active server
processes
2013-03-31 23:08:34.314 CEST 24864 WARNING:  terminating connection because
of crash of another server process
2013-03-31 23:08:34.314 CEST 24864 DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-03-31 23:08:34.314 CEST 24864 HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2013-03-31 23:08:34.315 CEST 24630 LOG:  all server processes terminated;
reinitializing
2013-03-31 23:08:34.326 CEST 24868 LOG:  database system was interrupted;
last known up at 2013-03-31 23:08:34 CEST
2013-03-31 23:08:34.326 CEST 24868 LOG:  database system was not properly
shut down; automatic recovery in progress
2013-03-31 23:08:34.330 CEST 24868 LOG:  record with zero length at
0/17B1778
2013-03-31 23:08:34.330 CEST 24868 LOG:  redo is not required
2013-03-31 23:08:34.347 CEST 24869 FATAL:  the database system is in
recovery mode
2013-03-31 23:08:34.363 CEST 24630 LOG:  database system is ready to accept
connections
2013-03-31 23:08:34.363 CEST 24874 LOG:  autovacuum launcher started =

lindebg@gmail.com writes:
> I would like to report a minimum combination of SQL, which crashes
> PostgreSQL 64 bit.

Thanks for the test case, will fix.

            regards, tom lane

I've bisected this down to this commit:

commit 0f61d4dd1b4f95832dcd81c9688dac56fd6b5687
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Fri Nov 19 17:31:50 2010 -0500

    Improve relation width estimation for subqueries.

And looking at the core dump,

#0  make_rel_from_joinlist (root=0xee4fb8, joinlist=<value optimized out>)
at allpaths.c:942
942                     if (IsA(jlnode, RangeTblRef))

jlnode holds 0x20, which is not a legal address.  Full backtrace at bottom
of this email.

Also, if I enable cassert on 0f61d4dd1b4f95, I get:

TRAP: FailedAssertion("!(te->resno >= rel->min_attr && te->resno <=
rel->max_attr)", File: "costsize.c", Line: 3217)

At which point, resno = 16, min_attr = 0, max_attr = 15

I don't where to take it from here, but maybe this info will help someone
else get there faster.

Cheers,

Jeff

#0  make_rel_from_joinlist (root=0xee4fb8, joinlist=<value optimized out>)
at allpaths.c:942
#1  0x00000000005ae856 in query_planner (root=0xee4fb8, tlist=<value
optimized out>, tuple_fraction=0, limit_tuples=-1,
cheapest_path=0x7fff041d8420,
    sorted_path=0x7fff041d8418, num_groups=0x7fff041d8428) at planmain.c:271
#2  0x00000000005b0304 in grouping_planner (root=<value optimized out>,
tuple_fraction=0) at planner.c:1144
#3  0x00000000005b1ee3 in subquery_planner (glob=0xe6bb20, parse=0xee1350,
parent_root=<value optimized out>, hasRecursion=<value optimized out>,
    tuple_fraction=0, subroot=0x7fff041d8568) at planner.c:535
#4  0x00000000005b57e4 in make_subplan (node=0xee10e0, context=<value
optimized out>) at subselect.c:378
#5  process_sublinks_mutator (node=0xee10e0, context=<value optimized out>)
at subselect.c:1680
#6  0x000000000057bc71 in expression_tree_mutator (node=<value optimized
out>, mutator=0x5b55e0 <process_sublinks_mutator>, context=0x7fff041d8640)
    at nodeFuncs.c:2060
#7  0x00000000005b5837 in process_sublinks_mutator (node=0xee1090,
context=0x7fff041d8730) at subselect.c:1770
#8  0x000000000057c09b in expression_tree_mutator (node=<value optimized
out>, mutator=0x5b55e0 <process_sublinks_mutator>, context=0x7fff041d8730)
    at nodeFuncs.c:1991
#9  0x00000000005b5837 in process_sublinks_mutator (node=0xee1060,
context=0x7fff041d8790) at subselect.c:1770
#10 0x00000000005b5d6a in SS_process_sublinks (root=<value optimized out>,
expr=<value optimized out>, isQual=<value optimized out>) at
subselect.c:1653
#11 0x00000000005aeebf in preprocess_expression (root=0xee0d10, expr=<value
optimized out>, kind=1) at planner.c:656
#12 0x00000000005b1cbd in subquery_planner (glob=0xe6bb20, parse=0xedc2f0,
parent_root=<value optimized out>, hasRecursion=<value optimized out>,
    tuple_fraction=0, subroot=0x7fff041d88b8) at planner.c:417
#13 0x0000000000599426 in set_subquery_pathlist (root=0xedba38,
rel=0xedbed8, rti=<value optimized out>, rte=<value optimized out>) at
allpaths.c:754
#14 set_rel_pathlist (root=0xedba38, rel=0xedbed8, rti=<value optimized
out>, rte=<value optimized out>) at allpaths.c:178
#15 0x00000000005997d3 in set_base_rel_pathlists (root=0xedba38,
joinlist=0xedc070) at allpaths.c:158
#16 make_one_rel (root=0xedba38, joinlist=0xedc070) at allpaths.c:94
#17 0x00000000005ae856 in query_planner (root=0xedba38, tlist=<value
optimized out>, tuple_fraction=0, limit_tuples=-1,
cheapest_path=0x7fff041d8b60,
    sorted_path=0x7fff041d8b58, num_groups=0x7fff041d8b68) at planmain.c:271
#18 0x00000000005b0304 in grouping_planner (root=<value optimized out>,
tuple_fraction=0) at planner.c:1144
#19 0x00000000005b1ee3 in subquery_planner (glob=0xe6bb20, parse=0xe6aa18,
parent_root=<value optimized out>, hasRecursion=<value optimized out>,
    tuple_fraction=0, subroot=0x7fff041d8c48) at planner.c:535
#20 0x00000000005b2191 in standard_planner (parse=0xe6aa18,
cursorOptions=0, boundParams=0x0) at planner.c:200
#21 0x0000000000602b4a in pg_plan_query (querytree=<value optimized out>,
cursorOptions=<value optimized out>, boundParams=<value optimized out>)
    at postgres.c:764
#22 0x0000000000602c34 in pg_plan_queries (querytrees=<value optimized
out>, cursorOptions=0, boundParams=0x0) at postgres.c:823
#23 0x0000000000603bf0 in exec_simple_query (query_string=0xe69bf0 "select
* from vinfo;") at postgres.c:988
#24 0x0000000000604a73 in PostgresMain (argc=<value optimized out>,
argv=<value optimized out>, username=<value optimized out>) at
postgres.c:3932
#25 0x00000000005d1bc9 in BackendRun () at postmaster.c:3561
#26 BackendStartup () at postmaster.c:3246
#27 ServerLoop () at postmaster.c:1432
#28 0x00000000005d41bc in PostmasterMain (argc=<value optimized out>,
argv=<value optimized out>) at postmaster.c:1093
#29 0x000000000057a210 in main (argc=4, argv=0xdbed00) at main.c:188

Cheers,

Jeff

Jeff Janes <jeff.janes@gmail.com> writes:
> I've bisected this down to this commit:
> commit 0f61d4dd1b4f95832dcd81c9688dac56fd6b5687

Oh, that's fixed already --- it went in just in time for the last set of
minor releases.  See d931ac0ec et al.

            regards, tom lane