Insert data size limit from PHP PDO to PGSQL

[lunarfish]

Hello people.

 

Apache 2.2, PHP 5.3.4, Zend Db 1.11.11, PostgreSQL 9.0.4

 

I am porting an application from MySQL to PGSQL using PHP with Zend Db / PDO for database abstraction.

 

My application enters a geometry column using the ST_GeomFromText() function.

 

With a small number of vertices this works fine but when a polygon has too many vertices PHP crashes (I get a connection reset error and apache restarts).

 

I have captured the insert statement and run it via pgAdmin III and that works fine. Since the problem only occurs once the polygon has sufficient vertices, I assume that there is some limit being applied either to the length of the expression or to the size of the data / insert query but this limit is only being applied via the client connection from PHP since running the same insert on pgAdmin works.

 

Does anyone know what limit I'm hitting and if there are any runtime configuration settings I can set in my PHP to increase it?

 

This is the table definition:

 

CREATE TABLE s2_site_boundary
(
  s2_site_boundary_id serial NOT NULL,
  s2_site_id integer NOT NULL,
  review_date date,
  boundary geometry,
  notes text,
  "version" double precision,
  createdby integer,
  createdon timestamp without time zone,
  CONSTRAINT s2_site_boundary_pkey PRIMARY KEY (s2_site_boundary_id),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(boundary) = 4326)
);

 

.. and Zend Db code which is failing:

 

$table = 's2_site_boundary';
$data = array(
   's2_site_id'   => 1,
   'review_date'  => '2012-01-25',
   'boundary'     => new Zend_Db_Expr("st_geomfromtext('POLYGON((-2.022588 53.397066,-2.019976 53.39693,-2.017444 53.396525,-2.015068 53.395863,-2.01292 53.394965,-2.011066 53.393857,-2.009563 53.392574,-2.008455 53.391154,-2.007776 53.38964,-2.007547 53.38808,-2.007775 53.386519,-2.008453 53.385005,-2.009561 53.383585,-2.011063 53.382302,-2.012916 53.381193,-2.015062 53.380294,-2.017437 53.379632,-2.019968 53.379226,-2.022578 53.379089,-2.025188 53.379225,-2.02772 53.37963,-2.030095 53.380292,-2.032242 53.38119,-2.034096 53.382297,-2.0356 53.38358,-2.036709 53.385,-2.037389 53.386513,-2.037618 53.388074,-2.037391 53.389635,-2.036714 53.391148,-2.035608 53.392569,-2.034105 53.393853,-2.032253 53.394961,-2.030106 53.39586,-2.027731 53.396523,-2.025199 53.396929,-2.022588 53.397066,-2.022588 53.397066,-2.022588 53.397066))',4326)"),
   'notes'        => 'T1',
   'version'      => 1.1,
   'createdby'    => 1
);
$res = $db->insert($table,$data);

 

.. replacing boundary column definition with this shorter one works fine:

 

'boundary'     => new Zend_Db_Expr("st_geomfromtext('POLYGON((-2.022588 53.397066,-2.019976 53.39693,-2.008453 53.385005,-2.012916 53.381193,-2.0356 53.38358,-2.036709 53.385,-2.022588 53.397066))',4326)"),

 

Thanks. Dan

[lunarfish]

Just tried running the same big insert without the Zend_Db PDO layer using the native pg_ functions and it worked with no problems. This issue does appear to be related to Zend_Db or PDO.

 

$dbconn = pg_connect("host=localhost dbname=? user=? password=?")
    or die('Could not connect: ' . pg_last_error());
// Performing SQL query           
$query = "INSERT INTO s2_site_boundary (s2_site_id,review_date,boundary,version) VALUES (1,'2012-01-26',st_geomfromtext('POLYGON((-2.022588 53.397066,-2.019976 53.39693,-2.017444 53.396525,-2.015068 53.395863,-2.01292 53.394965,-2.011066 53.393857,-2.009563 53.392574,-2.008455 53.391154,-2.007776 53.38964,-2.007547 53.38808,-2.007775 53.386519,-2.008453 53.385005,-2.009561 53.383585,-2.011063 53.382302,-2.012916 53.381193,-2.015062 53.380294,-2.017437 53.379632,-2.019968 53.379226,-2.022578 53.379089,-2.025188 53.379225,-2.02772 53.37963,-2.030095 53.380292,-2.032242 53.38119,-2.034096 53.382297,-2.0356 53.38358,-2.036709 53.385,-2.037389 53.386513,-2.037618 53.388074,-2.037391 53.389635,-2.036714 53.391148,-2.035608 53.392569,-2.034105 53.393853,-2.032253 53.394961,-2.030106 53.39586,-2.027731 53.396523,-2.025199 53.396929,-2.022588 53.397066,-2.022588 53.397066,-2.022588 53.397066))',4326),1.1);";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

pg_close($dbconn);

 

[foxy69]

Hi, just wondered if you found a solution to your problem.

 

I've created a PostgreSQL function that takes an XML document as a parameter and I'm seeing the same problem with the XML string being passed. If I play around with the tags I output the function runs, but if I supply all tags the browser just hangs.

 

I've used Zend Debug to output the SQL string, which when run in PgAdmin works fine. So it definetly looks like a ZEND/PDO issue.