Thread: simple query triggers abnormal termination

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]

"Sean Carmody" <sean@categoricalsolutions.com.au> writes:
> CREATE VIEW last_date AS SELECT max(date) AS last_date FROM test
> SELECT test.* FROM test,last_date WHERE date=last_date
> [crashes]

Yeah, it's a bug all right.  The rule rewriter has a lot of problems
with views that involve grouping or aggregation --- until it's fixed,
you need to be very wary of that combination of features.  A workaround
is to write the query without a rule:

select * from test where date = (select max(date) from test);

which is pretty grotty but I think it works reliably in 6.5.

BTW, in current sources the rule-using query doesn't crash, but it does
deliver the wrong answer :-(.  You get multiple copies of the desired
tuple.  Apparently the rewriter adds an extra RTE for table 'test' to
the top-level query:

CREATE VIEW last_date AS SELECT max(date) AS last_date FROM test vt;
explain SELECT t.* FROM test t,last_date l WHERE date=last_date;

Nested Loop  (cost=473.00 rows=10000 width=12)
  InitPlan
    ->  Aggregate  (cost=43.00 rows=1000 width=8)
      ->  Seq Scan on test vt  (cost=43.00 rows=1000 width=8)
  ->  Seq Scan on test t  (cost=43.00 rows=10 width=8)
  ->  Seq Scan on test vt  (cost=43.00 rows=1000 width=4)

EXPLAIN

> Now back to the thread...

>>>>>> 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.

Nonsense, that's a perfectly valid way of referring to a table.

            regards, tom lane