simple query triggers abnormal termination - Mailing list pgsql-bugs

I recently posted a problem to the novice list, but as the suggested fixes
didn't
solve my problem, I thought it may be time to escalate to the bug list.
Apologies
if the escalation is premature.

In short:

- I'm running PostgreSQL 6.5.2 under Linux kernel 2.0.35/Redhat 5.2.
- A simple select query caused pgsql to terminate with the following
message:

pqReadData() -- backend closed the channel unexpectedly.
     This probably means the backend terminated abnormally
     before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.

- The abnormal termination suggests that the root of the problem is not my
rough SQL.
- The details are in the thread from the novice list attached below.

Any comments greatly appreciated.
Sean Carmody

-- Thread attached -------

> > On Friday, 5 November 1999, Robert J. Sprawls wrote
> >
> > On Fri, 5 Nov 1999, Sean Carmody wrote:
> >
> > > I'm posting this to the novice list rather than the bug
> > > list just in case in just doing something foolish. The
> > > problem I had is as follows:
> >
> > [snip]

(An aside to un-snip the full problem again...)

I created a table something like this:

test:

Date                    Name        Age
---------------------------------------------
Wed Nov 03 19:11:21 1999 EST    Sean          30
Wed Nov 03 19:11:21 1999 EST    Sean          30
Wed Nov 03 19:11:21 1999 EST    Sean          30
Wed Nov 03 19:11:21 1999 EST    Sean          30
Wed Nov 03 19:11:22 1999 EST    Sean          30
Wed Nov 03 19:11:22 1999 EST    Sean          30
Wed Nov 03 19:11:36 1999 EST    Sean          30
Wed Nov 03 19:11:45 1999 EST    Sean          30
Wed Nov 03 19:35:28 1999 EST    Chris         30
Wed Nov 03 19:35:33 1999 EST    Chris         30
Wed Nov 03 19:35:35 1999 EST    Chris         30
Wed Nov 03 19:11:20 1999 EST    Dom           33
Wed Nov 03 19:11:20 1999 EST    Dom           33
Wed Nov 03 19:11:20 1999 EST    Dom           33

I then created the following view:

    CREATE VIEW last_date AS SELECT max(date) AS last_date FROM test

Checking this with "SELECT * from last_date" gave (as expected)

last_date
----------------------------
Wed Nov 03 19:11:20 1999 EST
(Row 1)


The following command then resulted in an abnormal termination

SELECT test.* FROM test,last_date WHERE date=last_date

Now back to the thread...

> > >
> > > The following command then resulted in a crash:
> > >
> > > SELECT test.* FROM test,last_date WHERE date=last_date
> >          ^^^^^^
> > Ah, your using an alias, but I don't see you declaring an alias
anywhere.

[snip]

> > Yes, you using test.* as an alias to test yet you didn't declare an
alias.
> > An example would be:
> >
> > SELECT t.date, t.name, t.age FROM test t, last_date l WHERE t.date =
> > l.date;

> Anyway, I gave your suggestion a try, or at least I tried the following:

> SELECT t.date, t.name, t.age FROM test t, max_date l WHERE t.date =
l.last_date;

> The result was again a crash. More precisely, pgsql exited with the
following
> message:

> pqReadData() -- backend closed the channel unexpectedly.
>     This probably means the backend terminated abnormally
>     before or while processing the request.
> We have lost the connection to the backend, so further processing is
impossible. Terminating.

[snip]