Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.1/8.9.1) with SMTP id DAA05540 for ; Thu, 14 Jan 1999 03:15:49 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m100hwj-000EBQC; Thu, 14 Jan 99 09:16 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] More view problems To: jproseve@arcavia.com (JP Rosevear) Date: Thu, 14 Jan 1999 09:16:04 +0100 (MET) Cc: pgsql-sql@postgreSQL.org, djackson@cpsgroup.com Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <369D7A93.EA0FC796@arcavia.com> from "JP Rosevear" at Jan 14, 99 00:03:15 am X-Mailer: ELM [version 2.4 PL25] Content-Type: text > > Building on DeJuan's suggestion, we have arrived at the following code > (using the extra view because from queries don't seem to allow sub > queries): > > create view tab021temp as select fldkey as fldkey2, fld000 as fld000, > fld001 as fld001 from tab000 group by fld001, fld000, fldkey2; > > create view tab021 as select count(1) as fldkey, a.fldkey2, a.fld000, > a.fld001 from tab021temp a, tab021temp b where a.fld001 > b.fld001 or > (a.fld001 = b.fld001 and a.fld000 > b.fld000) or a.fld000 = b.fld000 > grouPQexec() -- Request was sent to backend, but backend closed the > channel before rp by fld001, fld000, fldkey2; > > select * from tab021; > > This terminates in psql with the following error after the select > statement is made: > > PQexec() -- Request was sent to backend, but backend closed the channel > before r > esponding. This probably means the backend terminated abnormally before > or while pr ocessing the request. > > What is going on here? Multi level views seem to work if they are > simple. Why is the connection being dropped? JDBC gives up a similar > broken pipe error. There are still bugs in the rewrite system about handling of GROUP BY in views. It is because views aren't implemented in a form of subselect. Instead they replace expressions in the original query. I don't want to explain it in detail here (RTF-programmers-M). However deep you nest views, the final rewritten statement is still something you could have typed as a legal SQL statement - and such has only one possible grouping. Don't use GROUP BY in view definitions for now. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #