Обсуждение: backend crash with certain statements/tables

Reproduced in PG 7.4.1 on ...

OS X Server 10.2.8
OS X 10.2.8
RHL 9 (2.4.20-30.9)

Running any of these statements on my database causes the backend to
crash (example from PG log below):

create temp table foo as select * from server_prefs limit 1;
create table foo as select * from server_prefs limit 1;
create temp table foo as select * from agency_dbs limit 1;
create table foo as select * from agency_dbs limit 1;


All of these statements produce expected results (no crash):

create temp table foo as select * from c_group limit 1;
create table foo as select * from c_group limit 1;
create table foo as select * from server_prefs;
create table foo as select * from agency_dbs;
select * from server_prefs limit 1;
select * from agency_dbs limit 1;


Attached is a file that creates a database and only the server_prefs
table and then reproduces the crash for me on both OS X and Linux
when I run it like:

    psql -d template1 -U postgres -f pg_crash

Thanks,
- Jeff



2004-03-01 10:32:24 [471] LOG:  connection received: host=[local] port=
2004-03-01 10:32:24 [471] LOG:  connection authorized: user=username
database=cos
2004-03-01 10:32:34 [471] LOG:  statement: create temp table foo as
select * from server_prefs limit 1;
2004-03-01 10:32:35 [19913] LOG:  server process (PID 471) was
terminated by signal 10
2004-03-01 10:32:35 [19913] LOG:  terminating any other active server processes
2004-03-01 10:32:35 [429] WARNING:  terminating connection because of
crash of another server process
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.
HINT:  In a moment you should be able to reconnect to the database
and repeat your command.
2004-03-01 10:32:35 [474] LOG:  connection received: host=[local] port=
2004-03-01 10:32:35 [474] FATAL:  the database system is in recovery mode
2004-03-01 10:32:35 [19913] LOG:  all server processes terminated;
reinitializing
2004-03-01 10:32:35 [475] LOG:  database system was interrupted at
2004-03-01 10:27:58 MST
2004-03-01 10:32:35 [475] LOG:  checkpoint record is at 5/1350BF6C
2004-03-01 10:32:35 [475] LOG:  redo record is at 5/1350BF6C; undo
record is at 0/0; shutdown TRUE
2004-03-01 10:32:35 [475] LOG:  next transaction ID: 374025; next OID: 46414656
2004-03-01 10:32:35 [475] LOG:  database system was not properly shut
down; automatic recovery in progress
2004-03-01 10:32:35 [475] LOG:  redo starts at 5/1350BFAC
2004-03-01 10:32:35 [475] LOG:  unexpected pageaddr 5/9570000 in log
file 5, segment 19, offset 5701632
2004-03-01 10:32:35 [475] LOG:  redo done at 5/1356EB8C
2004-03-01 10:32:38 [475] LOG:  database system is ready


--

Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.

Jeff Bohmer <bohmer@visionlink.org> writes:
> Running any of these statements on my database causes the backend to
> crash (example from PG log below):

Crash confirmed here.  Seems to be a side-effect of the 7.4 optimization
that tries to avoid "unnecessary" projection steps.  In the "SELECT *
FROM foo LIMIT" case, the scan plan decides it need not do projection,
but actually one must occur (else heap_insert will scribble on the
source table :-().  The junkfilter code in execMain.c is supposed to
catch this situation, but it's fooled by the presence of a LIMIT step.
There may be some related cases, need to think more.

I should have a patch later today.

            regards, tom lane

>
>I should have a patch later today.

Great!  I'd like to try out the patch when it's ready.

Thanks,
- Jeff
--

Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.

Jeff Bohmer <bohmer@visionlink.org> writes:
> Great!  I'd like to try out the patch when it's ready.

Here ya go.

            regards, tom lane

Index: src/backend/executor/execAmi.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/executor/execAmi.c,v
retrieving revision 1.75.4.1
diff -c -r1.75.4.1 execAmi.c
*** src/backend/executor/execAmi.c    18 Dec 2003 20:21:53 -0000    1.75.4.1
--- src/backend/executor/execAmi.c    2 Mar 2004 18:52:18 -0000
***************
*** 348,350 ****
--- 348,415 ----
              return false;
      }
  }
+
+ /*
+  * ExecMayReturnRawTuples
+  *        Check whether a plan tree may return "raw" disk tuples (that is,
+  *        pointers to original data in disk buffers, as opposed to temporary
+  *        tuples constructed by projection steps).  In the case of Append,
+  *        some subplans may return raw tuples and others projected tuples;
+  *        we return "true" if any of the returned tuples could be raw.
+  *
+  * This must be passed an already-initialized planstate tree, because we
+  * need to look at the results of ExecAssignScanProjectionInfo().
+  */
+ bool
+ ExecMayReturnRawTuples(PlanState *node)
+ {
+     /*
+      * At a table scan node, we check whether ExecAssignScanProjectionInfo
+      * decided to do projection or not.  Most non-scan nodes always project
+      * and so we can return "false" immediately.  For nodes that don't
+      * project but just pass up input tuples, we have to recursively
+      * examine the input plan node.
+      *
+      * Note: Hash and Material are listed here because they sometimes
+      * return an original input tuple, not a copy.  But Sort and SetOp
+      * never return an original tuple, so they can be treated like
+      * projecting nodes.
+      */
+     switch (nodeTag(node))
+     {
+         /* Table scan nodes */
+         case T_SeqScanState:
+         case T_IndexScanState:
+         case T_TidScanState:
+         case T_SubqueryScanState:
+         case T_FunctionScanState:
+             if (node->ps_ProjInfo == NULL)
+                 return true;
+             break;
+
+         /* Non-projecting nodes */
+         case T_HashState:
+         case T_MaterialState:
+         case T_UniqueState:
+         case T_LimitState:
+             return ExecMayReturnRawTuples(node->lefttree);
+
+         case T_AppendState:
+         {
+             AppendState *appendstate = (AppendState *) node;
+             int            j;
+
+             for (j = 0; j < appendstate->as_nplans; j++)
+             {
+                 if (ExecMayReturnRawTuples(appendstate->appendplans[j]))
+                     return true;
+             }
+             break;
+         }
+
+         /* All projecting node types come here */
+         default:
+             break;
+     }
+     return false;
+ }
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/executor/execMain.c,v
retrieving revision 1.220.2.1
diff -c -r1.220.2.1 execMain.c
*** src/backend/executor/execMain.c    22 Jan 2004 02:23:35 -0000    1.220.2.1
--- src/backend/executor/execMain.c    2 Mar 2004 18:52:18 -0000
***************
*** 665,674 ****
      /*
       * Initialize the junk filter if needed.  SELECT and INSERT queries
       * need a filter if there are any junk attrs in the tlist.    INSERT and
!      * SELECT INTO also need a filter if the top plan node is a scan node
!      * that's not doing projection (else we'll be scribbling on the scan
!      * tuple!)    UPDATE and DELETE always need a filter, since there's
!      * always a junk 'ctid' attribute present --- no need to look first.
       */
      {
          bool        junk_filter_needed = false;
--- 665,674 ----
      /*
       * Initialize the junk filter if needed.  SELECT and INSERT queries
       * need a filter if there are any junk attrs in the tlist.    INSERT and
!      * SELECT INTO also need a filter if the plan may return raw disk tuples
!      * (else heap_insert will be scribbling on the source relation!).
!      * UPDATE and DELETE always need a filter, since there's always a junk
!      * 'ctid' attribute present --- no need to look first.
       */
      {
          bool        junk_filter_needed = false;
***************
*** 689,706 ****
                      }
                  }
                  if (!junk_filter_needed &&
!                     (operation == CMD_INSERT || do_select_into))
!                 {
!                     if (IsA(planstate, SeqScanState) ||
!                         IsA(planstate, IndexScanState) ||
!                         IsA(planstate, TidScanState) ||
!                         IsA(planstate, SubqueryScanState) ||
!                         IsA(planstate, FunctionScanState))
!                     {
!                         if (planstate->ps_ProjInfo == NULL)
!                             junk_filter_needed = true;
!                     }
!                 }
                  break;
              case CMD_UPDATE:
              case CMD_DELETE:
--- 689,697 ----
                      }
                  }
                  if (!junk_filter_needed &&
!                     (operation == CMD_INSERT || do_select_into) &&
!                     ExecMayReturnRawTuples(planstate))
!                     junk_filter_needed = true;
                  break;
              case CMD_UPDATE:
              case CMD_DELETE:
Index: src/include/executor/executor.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/executor/executor.h,v
retrieving revision 1.102.2.2
diff -c -r1.102.2.2 executor.h
*** src/include/executor/executor.h    22 Jan 2004 02:23:35 -0000    1.102.2.2
--- src/include/executor/executor.h    2 Mar 2004 18:52:19 -0000
***************
*** 36,41 ****
--- 36,42 ----
  extern void ExecRestrPos(PlanState *node);
  extern bool ExecSupportsMarkRestore(NodeTag plantype);
  extern bool ExecSupportsBackwardScan(Plan *node);
+ extern bool ExecMayReturnRawTuples(PlanState *node);

  /*
   * prototypes from functions in execGrouping.c

>Jeff Bohmer <bohmer@visionlink.org> writes:
>>  Great!  I'd like to try out the patch when it's ready.
>
>Here ya go.


Works for me on OS X and Linux.

Thank you very much!

- Jeff
--

Jeff Bohmer
VisionLink, Inc.
_________________________________
303.402.0170
www.visionlink.org
_________________________________
People. Tools. Change. Community.