Thread
-
postgresql process crashes on select * from cmd_sm (which is a view)
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-12-21T16:35:00Z
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-07 11: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 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 -
Re: postgresql process crashes on select * from cmd_sm (which is a view)
Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-21T17:41:27Z
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
-
Re: postgresql process crashes on select * from cmd_sm (which is a view)
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-12-21T19:12:01Z
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