CTTAS w/ DISTINCT ON crashes backend

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: CTTAS w/ DISTINCT ON crashes backend
Date: 2004-01-20 22:46:42
Message-ID: 400DAFD2.8050501@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I subscribed to pgsql-bugs and submitted a bug report about 10 hours
ago, but have yet to see my post go through, so I thought I'd through
this out on -general.

In a nutshell:

CREATE TEMPORARY TABLE foo AS
SELECT DISTINCT ON (x, y, z) *
FROM bar;

crashes the backend and screws up data pages associated with the catalog
under 7.4.1. It worked fine under 7.3.2. It always works when there
isn't any data. With my data however, it crashes the backend every time.

Just to be sure, I fsck'ed w/badblocks (-c -c) and ran memtest86 - no
errors. I reloaded the entire cluster from backup cleanly and executed
the same query again and it crashes at precisely the exact same place
using the above construct. No core images were left behind. If I get
enough time, I'll attach gdb and generate a backtrace. Rewritting the
query as:

CREATE TEMPORARY TABLE foo AS
SELECT *
FROM bar
LIMIT 0;

INSERT INTO foo
SELECT DISTINCT ON (x, y, z) *
FROM bar;

does not crash the backend and works as expected. 'bar', btw is also a
temporary table...

Mike Mascari

Browse pgsql-general by date

  From Date Subject
Next Message Marc G. Fournier 2004-01-21 00:55:20 Re: SCO Extortion
Previous Message Gavin M. Roy 2004-01-20 22:06:35 SCO Extortion