postgresql process crashes on select * from cmd_sm (which is a view)

Cristi Posoiu (cristi(at)auctionwatch(dot)ro) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
postgresql process crashes on select * from cmd_sm (which is a view)

Long Description
I have a problem with a postgreSQL view. You have the sql commands
that creates the tables, data in it and the view at the end of the
file. I modified some field names in the tables and also I have more
data in them, about 95000 rows in t_l and 2500 rows in t_p.

I have postgreSQL v 7.03, RPMs for redhat 6.2, installed on RedHat
6.2. I also modified in the /etc/rc.d/init.d/postgres and added
-o '-B 250 -S 4000' to the command line that starts the server.
I also have Linux kernel 2.2.16 on a Pentium processor.

PS: Unrelated to this, why select count(*) from some_table is taking
so long? Where some_table is a real table, not a view.
PPS: Also unrelated to this, how can I modify the primary key of a
table ? (I mean - telling who should be the new primary key) Besides
creating a new table and using COPY.

-------------------------------------------------------------
COMMANDS TO RUN:
cristi=# select count(*) from cmd_sm;
count
-------
(0 rows)

cristi=# select * from cmd_sm;
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.
!# \q

It seems that it crashes the process that was handling my query :-(
-------------------------------------------------------------
Another problem I have is that , on my data if I issue a command like:
select count(*) from cmd_sm;
I get something like that:
count
-------
5
12
35
76
228
14
7
9
52
6
2
....

What would represent that? I wanted the number of rows in the result
!?!
Also, I get THE SAME result if I use something like:
select count(name_of_the_field) from cmd_sm;
The results I get seems to be the values inside the last column of the
results.

-------------------------------------------------------------
SQL COMMANDS TO CREATE tables,data and view:

\connect - cristi
DROP TABLE "t_l";
CREATE TABLE "t_l" (
"txt_1" character varying(32),
"user_id" int4,
"id_1" int4,
"time_1" timestamp,
"number_1" numeric(10,2),
"number_2" numeric(10,2),
"number_3" int4,
"number_4" numeric(10,2),
"number_5" numeric(10,2),
"txt_2" character varying(80)
);
INSERT INTO "t_l" ("txt_1","user_id","id_1","time_1","number_1","number_2","number_3","number_4","number_5","txt_2")VALUES ('asdmksakdjskljdksljdksjdlkj',74840,2,'2000-12-07 00:00:09+02','5.95','0.00',1,'0.10','0.10','laksdjklsadjklsdjaskldjklsdjklsjd');

\connect - cristi
DROP TABLE "t_p";
CREATE TABLE "t_p" (
"user_id" int4,
"number_1" numeric(10,2),
"message" text,
"id_1" character varying(64),
"id_2" character varying(64),
"id_3" character varying(64),
"state" character varying(20),
"amount" numeric(10,2),
"final_amount" numeric(10,2),
"email" character varying(255),
"username" character varying(255),
"new_id_1" character varying(64),
"new_date" timestamp,
"new_something" bool,
"new_email" character varying(64)
);
INSERT INTO "t_p" ("user_id","number_1","message","id_1","id_2","id_3","state","amount","final_amount","email","username","new_id_1","new_date","new_something","new_email") VALUES (283,'3.50','','mingo','candymingo','','not processed','3.50','3.50','hm(at)somewhere(dot)com','mingo','mingo__','2000-12-07 11:04:48+02','t','hm(at)somewhere(dot)com');

DROP VIEW cmd_sm;

CREATE VIEW cmd_sm AS
SELECT a.user_id,
sum(CASE WHEN b.id_1 = 2 THEN 1 ELSE 0 END) as "# txt 1",

sum(CASE WHEN b.id_1 = 1 AND float8(b.number_1) < 8 AND float8(b.number_2) < 8 THEN 1 ELSE 0 END) as "# txt 2",

sum(CASE WHEN b.id_1 = 1 AND float8(b.number_1) between 8 and 20.99
AND float8(b.number_2) between 8 and 20.99 THEN 1 ELSE 0 END) as "# txt 3",

sum(CASE WHEN b.id_1 = 1 AND float8(b.number_1) BETWEEN 21 AND 40.99 AND float8(b.number_2) BETWEEN 21 AND 40.99 THEN 1 ELSE 0 END) as "# txt 4",

sum(CASE WHEN b.id_1 = 1 AND (float8(b.number_1) > 41 OR
float8(b.number_2) > 41) THEN 1 ELSE 0 END) as "# txt 5",

count(*) as "Total "

FROM t_p AS a, t_l AS b
WHERE a.user_id = b.user_id
GROUP BY a.user_id;

Sample Code

No file was uploaded with this report