Thread

1. Server Crash wit insert ... select ... group by

   Ramin Motakef <ramin@motakef.de> — 2001-03-18T12:46:05Z

   ============================================================================
                           POSTGRESQL BUG REPORT TEMPLATE
   ============================================================================
   
   
   Your name		:	Ramin Motakef
   Your email address	:       ramin@motakef.de
   
   
   System Configuration
   ---------------------
     Architecture (example: Intel Pentium)  	: AMD Athlon
   
     Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.4.2, Debian unstable
   
     PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.3
   
     Compiler used (example:  gcc 2.8.0)		:
   
   
   Please enter a FULL description of your problem:
   ------------------------------------------------
   
   The Following query causes the backend to crash:
   
   INSERT INTO bmonth (year,month,hours) 
   SELECT date_part('year',day), 
          date_part('month',day), 
          to_hour(sum(stop-start)) 
   FROM stunden 
   GROUP BY date_part('year',day), date_part('month',day);
   
   Error Message:
   
   pqReadData() -- backend closed the channel unexpectedly.
           This probably means the backend terminated abnormally
           before or while processing the request.
   The connection to the server was lost. Attempting reset: Failed.
   
   /var/log/postgresql:
   
   Server process (pid 10975) exited with status 11 at Sun Mar 18 13:00:20 2001
   Terminating any active server processes...
   Server processes were terminated at Sun Mar 18 13:00:20 2001
   Reinitializing shared memory and semaphores
   The Data Base System is starting up
   
   Description of tables below...
   
   
   Please describe a way to repeat the problem.   Please try to provide a
   concise reproducible example, if at all possible: 
   ----------------------------------------------------------------------
   
   CREATE TABLE "stunden" (
           "id" serial,
           "day" date,
           "start" time,
           "stop" time,
           "bem" text,
           PRIMARY KEY ("id")
   );
   
   CREATE TABLE "bmonth" (
           "year" int4,
           "month" int4,
           "hours" float8
   );
   
   CREATE FUNCTION "to_hour" (interval ) 
   RETURNS float8 
   AS 'select date_part(''day'',$1)*24 + date_part(''hour'',$1) +
           date_part(''min'',$1) / 60;' 
   LANGUAGE 'SQL';
   
   
   If you know how this problem might be fixed, list the solution below:
   ---------------------------------------------------------------------
   
   No solution, but creating a table in the select statement works:
   
   SELECT date_part('year',day) AS year,
           date_part('month',day) AS month, 
           to_hour(sum(stop-start)) AS hours 
   INTO mytest 
   FROM stunden 
   GROUP BY date_part('year',day), date_part('month',day)
   
   Thanks,
           Ramin
   
   

2. Re: Server Crash wit insert ... select ... group by

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-19T00:03:48Z

   Ramin Motakef <ramin@motakef.de> writes:
   > The Following query causes the backend to crash:
   
   > INSERT INTO bmonth (year,month,hours) 
   > SELECT date_part('year',day), 
   >        date_part('month',day), 
   >        to_hour(sum(stop-start)) 
   > FROM stunden 
   > GROUP BY date_part('year',day), date_part('month',day);
   
   This is fixed in 7.1.
   
   			regards, tom lane