PHP/PostgreSQL stops after 16 iterations in a loop? 1

[brendwal]

I am trying to trim apartment numbers off of an address field in a 800,000 record PostgreSQL table. It works for only 16 times through the for...loop then quits even though the results of the SELECT statement is 25,000. So logically it should run through the iteration 25,00 times. How come it craps out after 16 iterations of the for...loop?

Here is the script and result:

PHP Script

<?PHP
// Connect to PostgreSQL.
$db = pg_Connect( "host=192.168.41.14 dbname=BC_addresses user=postgres password=squid" );

$query = "select address, split_part(address,'-',2) as result_split from \"GVRD1\" WHERE \"address\" ilike '%-%' and \"city\" = 'Burnaby';";
$query_count = "SELECT count(address) FROM \"GVRD1\" WHERE \"address\" ilike '%-%' and \"city\" = 'Burnaby';";

$result = pg_Exec( $db, $query );
$result_count = pg_Exec( $db, $query_count );

$row_count = pg_Fetch_Row( $result_count, 0 );

echo "$row_count[0] results returned<br><br>";

// begin loop
for ($ii = 0; $ii < $row_count[0]; $ii++)
{
$row = pg_Fetch_Row( $result, $ii );
$addressResult = $row[0];
$splitaddressResult = $row[1];

$update_sql = "UPDATE \"GVRD1\" SET address = '$row[1]' where address = '$row[0]';";
echo "$row[0] originally - now converted to: <font color=yellow><b>$row[1]</font></b><br>";
$result_update = pg_Exec( $db, $update_sql );
}
pg_Close( $db );
?>


HTML OUTPUT

24899 results returned

304-9140 Halston Crt originally - now converted to: 9140 Halston Crt
2106-6837 Station Hill Dr originally - now converted to: 6837 Station Hill Dr
406-6742 Station Hill Crt originally - now converted to: 6742 Station Hill Crt
56-8701 16th Ave originally - now converted to: 8701 16th Ave
304-9584 Manchester Dr originally - now converted to: 9584 Manchester Dr
12-7128 18th Ave originally - now converted to: 7128 18th Ave
12-8277 11th Ave originally - now converted to: 8277 11th Ave
115-7428 19th Ave originally - now converted to: 7428 19th Ave
503-7151 Edmonds St originally - now converted to: 7151 Edmonds St
905-9603 Manchester Dr originally - now converted to: 9603 Manchester Dr
74-8701 16th Ave originally - now converted to: 8701 16th Ave
214-6745 Station Hill Crt originally - now converted to: 6745 Station Hill Crt
802-6888 Station Hill Dr originally - now converted to: 6888 Station Hill Dr
1607-9633 Manchester Dr originally - now converted to: 9633 Manchester Dr
1009-6837 Station Hill Dr originally - now converted to: 6837 Station Hill Dr
30-7179 18th Ave originally - now converted to: 7179 18th Ave
8-7175 17th Ave originally - now converted to: 7175 17th Ave

 

[jpadie]

could it be that the 17th row of your original source has some characters that need to be escaped before inserted into the db through the update? prob not the case as you are echoing before updating but worth a look?

suggest also that you change the loop to a more traditional form just in case:

while ($row = pg_Fetch_Row($result))
{
 //stuff inside the loop.
}

 

[DRJ478]

Crap out? Could you explain that a bit more:
Error messages? ANy other output? Does it leave the loop?
Ant more info would be welcome.

 

[brendwal]

More info:

Ok, after trying a few more runs of it, it stops with NO errors after 14-17 iterations of the for loop. The addresses don't have any strange escape characters or anything to cause it to bail out of the loop prematurly. I've run it 50 times, and it started at 17 times, now down to 14.

 

[jpadie]

read sleipnir214's FAQ on debugging, follow his advice on shoving loads of debug output into your loops etc (echo the sql queries, turn error_reporting(E_ALL) on etc). your code doesn't appear wrong but check before deeper investigations.

if all is still right with your code, have a look at your db logs to see what is happening - could your db connection be timing out?

 

[DRJ478]

Just for a test: Take out the UPDATE statement and see if the entire set is listed. That will clear up if there is something happening to the resource while the update is executed.

 

[brendwal]

Ok, upon further testing, it does NOT leave the loop, and if I comment out the UPDATE execution, the iteration works PAST the 14-17 times and works properly and exits the loop. I loaded the script with error handling originally and it throws NO errors. I stripped out the error handling for this post.
So therefore the UPDATE is causing some problem after run 14-17 times. ????????

 

[DRJ478]

There is no mention of a pg_Exec function in the PHP documentation. It is likely that it is a function that has the key to this behavior in it. Can you post it?

 

[brendwal]

Yes, pg_Exec is just a PostgreSQL statement to execute an SQL statement. Here is the official doc on it:

pg_exec
Name
pg_exec -- send a command string to the server
Synopsis
pg_exec dbHandle queryStringInputs

dbHandle
Specifies a valid database handle.

queryString
Specifies a valid SQL query.

Outputs

resultHandle
A Tcl error will be returned if pgtcl was unable to obtain a backend response. Otherwise, a query result object is created and a handle for it is returned. This handle can be passed to pg_result to obtain the results of the query.

Description
pg_exec submits a query to the PostgreSQL backend and returns a result. Query result handles start with the connection handle and add a period and a result number.

Note that lack of a Tcl error is not proof that the query succeeded! An error message returned by the backend will be processed as a query result with failure status, not by generating a Tcl error in pg_exec.

 

[jpadie]

could you try replacing it with the php version of pg_query(connection,querystring)?

 

[brendwal]

Tried the pg_query as stated above - same response. Down to 11 iterations now. It's decreasing every 20 or so runs of the script. What's going on? No errors in Apache Log. No timeouts - I increased that in Apache http.conf file. It doesn't get out of the loop and doesn't through an error.....ug!!!!

 

[jpadie]

what about the postgre logs? i still suspect that the db connection is closing on you. add some debug code into the query statements to give you some feedback on failure eg

pg_query(querystring)
 or die("query failed. postgre error was: " . pg_last_error());

complete shot in the dark, could you try a pg_pconnect instead of the pg_connect function above.

 

[brendwal]

Tried pg_pconnect instead of pg_connect - no change. Also tried the or die statement above - no dice. Still works for 10 iterations and stops, no error thrown - no postresql error log recorded or Apache error log recorded. I'm stumped.

 

[brendwal]

I tried the pg_pconnect instead, but still didn't work. Also tried the error reporting above along with more echo'ing and get this as my SQL UPDATE statement:

20,532 results returned

1204-1875 Robson St originally - now converted to: 1875 Robson St
UPDATE "GVRD1" SET address = '1875 Robson St' where address = '1204-1875 Robson St';
303-1860 Robson St originally - now converted to: 1860 Robson St
UPDATE "GVRD1" SET address = '1860 Robson St' where address = '303-1860 Robson St';
703-1905 Robson St originally - now converted to: 1905 Robson St
UPDATE "GVRD1" SET address = '1905 Robson St' where address = '703-1905 Robson St';
805-1875 Robson St originally - now converted to: 1875 Robson St
UPDATE "GVRD1" SET address = '1875 Robson St' where address = '805-1875 Robson St';
605-1950 Robson St originally - now converted to: 1950 Robson St
UPDATE "GVRD1" SET address = '1950 Robson St' where address = '605-1950 Robson St';
1203-1905 Robson St originally - now converted to: 1905 Robson St
UPDATE "GVRD1" SET address = '1905 Robson St' where address = '1203-1905 Robson St';
302-1525 Robson St originally - now converted to: 1525 Robson St
UPDATE "GVRD1" SET address = '1525 Robson St' where address = '302-1525 Robson St';
507-1933 Robson St originally - now converted to: 1933 Robson St
UPDATE "GVRD1" SET address = '1933 Robson St' where address = '507-1933 Robson St';
501-1749 Robson St originally - now converted to: 1749 Robson St
UPDATE "GVRD1" SET address = '1749 Robson St' where address = '501-1749 Robson St';
2-1520 Robson St originally - now converted to: 1520 Robson St
UPDATE "GVRD1" SET address = '1520 Robson St' where address = '2-1520 Robson St';
303-1875 Robson St originally - now converted to: 1875 Robson St
UPDATE "GVRD1" SET address = '1875 Robson St' where address = '303-1875 Robson St';
701-1875 Robson St originally - now converted to: 1875 Robson St
UPDATE "GVRD1" SET address = '1875 Robson St' where address = '701-1875 Robson St';
205-1749 Robson St originally - now converted to: 1749 Robson St
UPDATE "GVRD1" SET address = '1749 Robson St' where address = '205-1749 Robson St';
504-1749 Robson St originally - now converted to: 1749 Robson St
UPDATE "GVRD1" SET address = '1749 Robson St' where address = '504-1749 Robson St';



AND THAT's IT! It stops after 10 times, with no error thrown and doesn't ecen exit the loop to go onto the next statement which as echo statement saying it has exited the loop.

 

[jpadie]

still fishing in the dark here: could php be timing out? also why are you getting a different row count back than before?

is the address field indexed? (and if so is it uniquely indexed - which would cause a problem (unlikely given the output above) - or indexed with duplicates allowed - which would give a performance increase on this script)

 

[brendwal]

I think your fishing in the dark may have some validity! I do think PHP is timing out. Is there a PHP command to set timeout to zero or something?

 

[jpadie]

not to zero. look in php.ini for the timeout values. also in the postgre ini file for the connect time out value.

remember to restart the server after editing php.ini (if you have installed as a server mod).

 

[brendwal]

Thanks - it was the PHP timeout problem. A PHP script will timeout after 30 seconds. So I had to set the set_time_limit() very high like 10000 and it worked beautifully. Of course, I also figured out to index the dang DB also which sped things up - oops! lol!
Thanks for all your help....