CTTAS w/ DISTINCT ON crashes backend

[Mike Mascari]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)