Re: CTTAS w/ DISTINCT ON crashes backend

Tom Lane wrote:

>>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.
>>
>>
>
>Works for me ...
>
>
...

>Perhaps providing a specific test case would help.
>
>
Could you give me a bit of direction?

I dumped the data associated with the tables involved from database
"Development", loaded them into a new database "Test" and ran the script
which causes the backend to crash and it worked fine, no errors. This
was on the same machine.

I then run the same script against the database "Development" (from
which I had just dumped the data and relevant schema for "Test") and I
get a crashed backend. Here's a backtrace:

Program received signal SIGSEGV, Segmentation fault.
0x0806fb52 in nocachegetattr ()

#0 0x0806fb52 in nocachegetattr ()
#1 0x0810186e in execTuplesMatch ()
#2 0x081115dc in ExecUnique ()
#3 0x08104cf8 in ExecProcNode ()
#4 0x0810356d in ExecutePlan ()
#5 0x08102968 in ExecutorRun ()
#6 0x08178682 in ProcessQuery ()
#7 0x08179144 in PortalRunMulti ()
#8 0x08178afb in PortalRun ()
#9 0x08175545 in exec_simple_query ()
#10 0x08177c09 in PostgresMain ()
#11 0x08151b9b in BackendFork ()
#12 0x081515a3 in BackendStartup ()
#13 0x0814faa8 in ServerLoop ()
#14 0x0814f171 in PostmasterMain ()
#15 0x0811f5b5 in main ()
#16 0x42015967 in __libc_start_main () from /lib/i686/libc.so.6
(gdb)

I don't get it. I had received this error in the "Development" database
while running the application. I thought perhaps it was bad blocks or
flaky RAM. So I *wiped out* the database cluster after running fsck and
restored from the "Production" database dump copied from another
machine. I ran the application again and it crashed at the exact same place.

I can send you the query and the schema, but as I've said, when I load
the schema & data associated with the tables and views involved with
this query from "Development" into a new "Test" database in the same
cluster, it executes fine???

I'll try and dump the entire database and restore it on a third machine
and see if the query crashes that backend as well. But it will take a
bit of time. If it does crash what does that mean? If not, what does
that mean?

Mike Mascari