8.2.4 signal 11 with large transaction - Mailing list pgsql-general

The attached PHP script is a derived test case based on an actual problem
we've been seeing in our application.

The result of this script is a crashed (sig 11) backend on pg 8.2.4.

I've now reproduced this on two different systems, a large server with
1G of shared_buffers and many other performance tunings, and my
workstation, which has a pretty much default install of PG.  (The
problem also occurred on 8.2.0, which led to an upgrade but no
resolution)

On 8.1, the query fails but instead of the back end crashing, I get
a log message "[warning] postgres[49484]: [3-1] ERROR:  out of memory"

Oddly, the query succeeds if it's fed into psql.

I'm now full of mystery and wonder.  It would appear as if the
underlying problem has something to do with PHP, but why should this
cause a backend process to crash?

<?php

ini_set("memory_limit", "64M");

$table_name = "some_table1";
$create_table = "
CREATE TABLE $table_name (
  " . $table_name . "_id bigserial PRIMARY KEY,
  field1 varchar(50),
  field2 varchar(50),
  field3 varchar(50),
  field4 varchar(10),
  field5 date,
  field6 varchar(32),
  field7 varchar(100),
  field8 varchar(50),
  field9 date,
  field10 date,
  field11 varchar(50),
  field12 date,
  field13 varchar(100),
  field14 varchar(100),
  field15 varchar(100),
  field16 varchar(100),
  field17 varchar(100),
  field18 varchar(100),
  field19 varchar(50),
  field20 varchar(50),
  field21 varchar(12),
  field22 varchar(100),
  field23 varchar(50),
  field24 varchar(10),
  field25 varchar(50),
  field26 varchar(50),
  field27 varchar(50),
  field28 varchar(10),
  field29 varchar(200)
);
";

$data = array(
  'thomas',
  'norman',
  'anderson',
  '',
  '03/05/1965',
  '1234 road rd',
  '',
  'ST',
  '05/01/2004',
  '05/31/2007',
  '12345',
  '07/01/2009',
  'In 1972, a crack commando unit was sent to prison by a military court for a ',
  'crime they didnt commit. They promptly escaped from a maximum security ',
  'stockade to the Los Angeles underground. Today, still wanted by the ',
  'government, they survive as soldiers of fortune. If you have a problem, if ',
  'no-one else can help, and if you can find them, maybe you can hire the ',
  'A-Team.',
  'In 1972, a crack commando unit was sent',
  'crime they didnt commit. They promptly escaped',
  'laz0r',
  'stockade to the Los Angeles underground. Today',
  'government, they survive as soldiers of fortune',
  'Y',
  'no-one else can help, and if you can find them',
  'A-Team.',
  'maybe you can hire',
  'A-Team',
  'Today, still wanted by the
  government, they survive as soldiers of fortune. If you have a problem, if
  no-one else can help, and if you can find them, maybe you can hire the
  A-Team.'
);

$conn_string = "host=/tmp dbname=test user=pgsql";
$conn = pg_connect($conn_string);

pg_query($conn, "DROP TABLE $table_name");
pg_query($conn, $create_table);

$insert_prefix = "INSERT INTO $table_name ( ";
for($i=1; $i<30; $i++) {
  $insert_prefix .= "field" . $i;
  if ( $i < 29 ) {
    $insert_prefix .= ",";
  }
}
$insert_prefix .= " ) VALUES ( ";
$insert_suffix = " );\n";

$sql = "BEGIN WORK;\n";
for($i=0; $i<=30000; $i++) {
  if ( $i % 1000 == 0 ) {
    echo $i;
  }
  if ( $i % 200 == 0 ) {
    echo ".";
  }
  $sql .= $insert_prefix;
  for($j=0; $j<count($data); $j++) {
    if ( strlen($data[$j]) > 0 ) {
      $sql .= "'" . $data[$j] . "'";
    }
    else {
      $sql .= "NULL";
    }
    if ( $j < count($data) - 1 ) {
      $sql .= ",";
    }
  }
  $sql .= $insert_suffix;
}
$sql .= "COMMIT WORK;\n";

echo "\nQuerying..\n";

pg_query($sql);

echo "\n";

?>


--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023