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

Cristi Posoiu (cristi@auctionwatch.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@somewhere.com','mingo','mingo__','2000-12-0711:04:48+02','t','hm@somewhere.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
1ELSE 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

pgsql-bugs@postgresql.org writes:
> postgresql process crashes on select * from cmd_sm (which is a view)

Poking into this revealed what seems to be a long-standing bug: a view
that uses BETWEEN gets confused about any variables appearing in the
first argument of the BETWEEN.  I've fixed this in current sources,
but for 7.0.* you'd be best advised to write out BETWEEN explicitly
as "a >= b AND a <= c".

A separate issue is that grouped views don't work very well in 7.0.*.
This is also fixed for 7.1, but in the meantime don't expect to be able
to apply aggregates or grouping to the output of a grouped view.

            regards, tom lane

Added to TODO:

    * Prevent BETWEEN from using duplicate nodes

> pgsql-bugs@postgresql.org writes:
> > postgresql process crashes on select * from cmd_sm (which is a view)
>
> Poking into this revealed what seems to be a long-standing bug: a view
> that uses BETWEEN gets confused about any variables appearing in the
> first argument of the BETWEEN.  I've fixed this in current sources,
> but for 7.0.* you'd be best advised to write out BETWEEN explicitly
> as "a >= b AND a <= c".
>
> A separate issue is that grouped views don't work very well in 7.0.*.
> This is also fixed for 7.1, but in the meantime don't expect to be able
> to apply aggregates or grouping to the output of a grouped view.
>
>             regards, tom lane
>


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026