Обсуждение: BUG #3301: explain crashes server on simple (?) query

The following bug has been logged online:

Bug reference:      3301
Logged by:          Leszek Trenkner
Email address:      olaf@post.pl
PostgreSQL version: 8.3devel
Operating system:   Linux (Ubuntu Fiesty 7.04)
Description:        explain crashes server on simple (?) query
Details:

When I try to EXPLAIN or EXPLAIN analyze following query, server reports
crash and restarts (output from psql session follows):
------------------------------------------------
crash=# explain select t.id, foo.name from t join
(select ta.id, ta.name from ta
union select tb.id, tb.name from tb
) foo on foo.id = any (t.ids);
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>
------------------------------------------------
But, if I change UNION to UNION ALL  it works as expected:
------------------------------------------------
crash=# explain select t.id, foo.name from t join
(select ta.id, ta.name from ta
union all select tb.id, tb.name from tb
) foo on foo.id = any (t.ids);
                          QUERY PLAN
-------------------------------------------------------
 Nested Loop  (cost=1.05..4.09 rows=1 width=10)
   Join Filter: (foo.id = ANY (t.ids))
   ->  Append  (cost=0.00..2.06 rows=6 width=10)
         ->  Seq Scan on ta  (cost=0.00..1.03 rows=3 width=10)
         ->  Seq Scan on tb  (cost=0.00..1.03 rows=3 width=10)
   ->  Materialize  (cost=1.05..1.10 rows=5 width=47)
         ->  Seq Scan on t  (cost=0.00..1.05 rows=5 width=47)
(7 rows)
-------------------------------------------------------
This all on today's (2007-05-24) CSV 8.3 development version. The same bug
was in SVN version build from CSV on 2007-04-27. The server's log says:
-----------------------------------------
2007-05-24 02:27:44 CEST <@ > : LOG:  server process (PID 18536) was
terminated by signal 11: Segmentation fault
2007-05-24 02:27:44 CEST <@ > : LOG:  terminating any other active server
processes
2007-05-24 02:27:44 CEST <postgres@xxx postgres> idle: WARNING:  terminating
connection because of crash of another server process
2007-05-24 02:27:44 CEST <postgres@xxx postgres> idle: DETAIL:  The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2007-05-24 02:27:44 CEST <postgres@xxx postgres> idle: HINT:  In a moment
you should be able to reconnect to the database and repeat your command.
.
.
.
2007-05-24 02:27:44 CEST <@ > : LOG:  all server processes terminated;
reinitializing
---------------------------------------------------
I'll post schema for bug recreation on pgsql-bugs.

Greetings,
Leszek Trenkner

"Leszek Trenkner" <olaf@post.pl> writes:
> I'll post schema for bug recreation on pgsql-bugs.

Yes?

            regards, tom lane

Tom Lane wrote:

> "Leszek Trenkner" <olaf@post.pl> writes:
>> I'll post schema for bug recreation on pgsql-bugs.
>
> Yes?
>
> regards, tom lane

Well, I bug appeared on list with a long delay, so in the morning I didn't
notice it yet. Database dump that allows me to recreate situation comes
attached. It's a really simple query, and today I reproduced the very same
crash  on different machine.

It doesn't happen on stock Ubuntu's 8.2 ("PostgreSQL 8.2.4 on
i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 (Ubuntu
4.1.2-0ubuntu4)").

Server log with 'debug5' level:

"PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)"
--------------------------------------------------------------------------
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: DEBUG:  00000:
StartTransactionCommand
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: LOCATION:
start_xact_command, postgres.c:2248
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: STATEMENT:
explain select t.id, foo.name from t join
        (select ta.id, ta.name from ta
        union select tb.id, tb.name from tb
        ) foo on foo.id = any (t.ids);

2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: DEBUG:  00000:
StartTransaction
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: LOCATION:
ShowTransactionState, xact.c:4031
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: STATEMENT:
explain select t.id, foo.name from t join
        (select ta.id, ta.name from ta
        union select tb.id, tb.name from tb
        ) foo on foo.id = any (t.ids);

2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: DEBUG:  00000:
name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid:
757/1/0, nestlvl: 1, children: <>
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: LOCATION:
ShowTransactionStateRec, xact.c:4056
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: STATEMENT:
explain select t.id, foo.name from t join
        (select ta.id, ta.name from ta
        union select tb.id, tb.name from tb
        ) foo on foo.id = any (t.ids);

2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> EXPLAIN: DEBUG:  00000:
ProcessUtility
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> EXPLAIN: LOCATION:
PortalRunUtility, pquery.c:1142
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> EXPLAIN: STATEMENT:
explain select t.id, foo.name from t join
        (select ta.id, ta.name from ta
        union select tb.id, tb.name from tb
        ) foo on foo.id = any (t.ids);

2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: reaping dead processes
2007-05-24 18:55:50 CEST <@ > : LOCATION:  reaper, postmaster.c:2022
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: server process (PID 9253) was
terminated by signal 11: Segmentation fault
2007-05-24 18:55:50 CEST <@ > : LOCATION:  LogChildExit, postmaster.c:2460
2007-05-24 18:55:50 CEST <@ > : LOG:  00000: server process (PID 9253) was
terminated by signal 11: Segmentation fault
2007-05-24 18:55:50 CEST <@ > : LOCATION:  LogChildExit, postmaster.c:2460
2007-05-24 18:55:50 CEST <@ > : LOG:  00000: terminating any other active
server processes
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2332
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: sending SIGQUIT to process
9248
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2369
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: sending SIGQUIT to process
9247
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2369
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: sending SIGQUIT to process
9240
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2383
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: sending SIGQUIT to process
9242
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2395
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: sending SIGQUIT to process
9241
2007-05-24 18:55:50 CEST <@ > : LOCATION:  HandleChildCrash,
postmaster.c:2417
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: reaping dead processes
2007-05-24 18:55:50 CEST <@ > : LOCATION:  reaper, postmaster.c:2022
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: WARNING:  57P02:
terminating connection because of crash of another server process
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: DETAIL:  The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: HINT:  In a moment
you should be able to reconnect to the database and repeat your command.
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 crash> idle: LOCATION:
quickdie, postgres.c:2405
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 postgres> idle: WARNING:
57P02: terminating connection because of crash of another server process
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 postgres> idle: DETAIL:  The
postmaster has commanded this server process to roll back the current
transaction and exit, because another server process exited abnormally and
possibly corrupted shared memory.
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 postgres> idle: HINT:  In a
moment you should be able to reconnect to the database and repeat your
command.
2007-05-24 18:55:50 CEST <postgres@127.0.0.1 postgres> idle: LOCATION:
quickdie, postgres.c:2405
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: reaping dead processes
2007-05-24 18:55:50 CEST <@ > : LOCATION:  reaper, postmaster.c:2022
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: server process (PID 9247)
exited with exit code 2
2007-05-24 18:55:50 CEST <@ > : LOCATION:  LogChildExit, postmaster.c:2440
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: server process (PID 9248)
exited with exit code 2
2007-05-24 18:55:50 CEST <@ > : LOCATION:  LogChildExit, postmaster.c:2440
2007-05-24 18:55:50 CEST <@ > : LOG:  00000: all server processes
terminated; reinitializing
2007-05-24 18:55:50 CEST <@ > : LOCATION:  reaper, postmaster.c:2228
2007-05-24 18:55:50 CEST <@ > : DEBUG:  00000: shmem_exit(0)
2007-05-24 18:55:50 CEST <@ > : LOCATION:  shmem_exit, ipc.c:155
----------------------------------------------------------------------------------------
Well, previous crash report was in fact from Debian 4.0 host, not Ubuntu:
"PostgreSQL 8.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)"
--
Greetings. Leszek Trenkner

Leszek Trenkner <olaf@post.pl> writes:
> Database dump that allows me to recreate situation comes
> attached. It's a really simple query, and today I reproduced the very same
> crash  on different machine.

Fixed, thanks for the report!

> It doesn't happen on stock Ubuntu's 8.2

No, this is a new disease in 8.3devel.

            regards, tom lane