Jump to content

Insert data size limit from PHP PDO to PGSQL


lunarfish

Recommended Posts

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

Link to comment
Share on other sites

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);

 

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.