[BUGS] Server Crash wit insert ... select ... group by

[Ramin Motakef]

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :       Ramin Motakef
Your email address      :       [EMAIL PROTECTED]


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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster