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 aliasanywhere.
Nonsense, that's a perfectly valid way of referring to a table.
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofFri5Nov1999162248+1100000601bf274d$ccab32c0$0401a8c0@categoricalsolutions.com.au | Resolved by subject fallback