Thread: Duplicate INSERTS into pgsql table via PHP
I asked about this on the PHP list but no-one seemed to know what could be causing the problem. I'm setting up a Web-enabled database using PostgreSQL 6.5.3 and communicating with it via PHP 3.0.12 as an Apache module for Apache 1.3.9, all running on a Mandrake Linux 6.1 system. Any PHP page that reads from a Postgres table works fine, as does any table manipulation using the psql terminal monitor. But the big problem is if I use a PHP page that does an INSERT on a table, the table ends up with two rows being inserted instead of one. For example, for a table with columns of an int and varchar, the code $conn = pg_connect("", "", "", "","cczdao"); $result = pg_exec($conn, "insert into mytest values (333, 'some text')"); pg_close($conn); causes two rows, each with values 333 and 'some text' to be inserted. For PHP code inserting values where the int value is defined as max(intcolumn)+1, or where the table's int column is allowed to use its default of the nextval of some sequence, the inserted rows have consecutive values in that column. Running the Postgres "postmaster" process in debug mode shows two separate backends being started up, with consecutive pid's, each one doing an INSERT. I found from my Apache access log that these are due to two consecutive requests for the same page, when using Netscape (4.7 on Linux, 4.5 on Solaris). Yet this does NOT happen using Lynx, which produces one request for the page and hence only results in one record being inserted. Checking further shows that even a page which only calls pg_exec to do a SELECT from the table gets requested twice by Netscape, but only once by Lynx. Any other PHP pages, such as one which only calls phpinfo(), are requested once by both Netscape and Lynx. So it's only the pages which are accessing Postgres which provoke two consecutive requests, as logged by Apache, when requested by Netscape (and if those pages are running an INSERT query, result in two records being inserted into the table). I've got Netscape set to not use proxies (direct connection to Internet), in case our usual proxy autoconfig script got in the way; also, it makes no difference whether I allow local disk or memory caching, nor whether the document freshness is checked every time, once/ session or never. Has anyone got any suggestions? I'm running out of ideas of what to try next. thanks -- David Osborne david.osborne@nottingham.ac.uk Academic Computing Services phone/voicemail: +44 (0)115 951 3397 The University of Nottingham fax: +44 (0)115 951 3358 Nottingham NG7 2RD, UK http://www.nottingham.ac.uk/~cczdao/
I am running a very similiar setup (modified RedHat 5.2, Apache 1.3.9, PHP 3.0.12, PostgreSQL 6.5.2) and I cut and pasted your example into tester.php3 and created a database cczdao and a table mytest as: create table mytest(foo int, bar text); And it worked just fine. Perhaps there is something else that is going wrong? Could you perhaps give a little more info. Are there any redirects happening, does the same script generate the form and insert the data into the table? Also, I am curious as to why you are not using the nifty persistent connection pg_pconnect function? Jason Earl David Osborne wrote: > > I asked about this on the PHP list but no-one seemed to know what could be causing the problem. > > I'm setting up a Web-enabled database using PostgreSQL 6.5.3 and > communicating with it via PHP 3.0.12 as an Apache module for Apache > 1.3.9, all running on a Mandrake Linux 6.1 system. > > Any PHP page that reads from a Postgres table works fine, as does any > table manipulation using the psql terminal monitor. But the big problem > is if I use a PHP page that does an INSERT on a table, the table ends up > with two rows being inserted instead of one. > > For example, for a table with columns of an int and varchar, the code > > $conn = pg_connect("", "", "", "","cczdao"); > $result = pg_exec($conn, "insert into mytest values (333, 'some text')"); > pg_close($conn); > > causes two rows, each with values 333 and 'some text' to be inserted. > For PHP code inserting values where the int value is defined as > max(intcolumn)+1, or where the table's int column is allowed to use its > default of the nextval of some sequence, the inserted rows have > consecutive values in that column. > > Running the Postgres "postmaster" process in debug mode shows two > separate backends being started up, with consecutive pid's, each one > doing an INSERT. I found from my Apache access log that these are due to > two consecutive requests for the same page, when using Netscape (4.7 on > Linux, 4.5 on Solaris). Yet this does NOT happen using Lynx, which > produces one request for the page and hence only results in one record > being inserted. > > Checking further shows that even a page which only calls pg_exec to do a > SELECT from the table gets requested twice by Netscape, but only once by > Lynx. Any other PHP pages, such as one which only calls phpinfo(), are > requested once by both Netscape and Lynx. So it's only the pages which > are accessing Postgres which provoke two consecutive requests, as logged > by Apache, when requested by Netscape (and if those pages are running an > INSERT query, result in two records being inserted into the table). > > I've got Netscape set to not use proxies (direct connection to > Internet), in case our usual proxy autoconfig script got in the way; > also, it makes no difference whether I allow local disk or memory > caching, nor whether the document freshness is checked every time, once/ > session or never. > > Has anyone got any suggestions? I'm running out of ideas of what to try > next. > > thanks > -- > David Osborne david.osborne@nottingham.ac.uk > Academic Computing Services phone/voicemail: +44 (0)115 951 3397 > The University of Nottingham fax: +44 (0)115 951 3358 > Nottingham NG7 2RD, UK http://www.nottingham.ac.uk/~cczdao/ > > ************
jearl@box100.com said: > I am running a very similiar setup (modified RedHat 5.2, Apache 1.3.9, > PHP 3.0.12, PostgreSQL 6.5.2) and I cut and pasted your example into > tester.php3 and created a database cczdao and a table mytest as: > > create table mytest(foo int, bar text); > > And it worked just fine. Thanks for trying to reproduce my problem. > Perhaps there is something else that is going > wrong? Could you perhaps give a little more info. Are there any > redirects happening No 301 entries appear in the Apache access log. > does the same script generate the form and insert > the data into the table? No, in the case of the form page I started working on, the PHP page which does the insert is defined as an action: <FORM ACTION="referral_create.php3"> But in the test I gave as an example in my message, no form is involved, just a page whose embedded PHP code opens the database connection, does the insert and closes the connection -- I've appended it below. Yet it still gets requested twice when browsed by Netscape. The most puzzling thing is why the page is only requested once when browsed using Lynx. Here's what I mean, from the access log. The connections from 128.243.101.85 are using Lynx, those from 128.243.40.194 are using Netscape 4.5. The requests from Lynx for the pages pgselect.php3 and pginsert.php3 are each logged once, while requests for the same pages from Netscape are logged twice. "pgselect.php3" just connects and executes a select on the table, to show its contents; "pginsert.php3" is shown below and is the one which executes the insert statement. 128.243.101.85 - - [14/Dec/1999:13:41:53 +0000] "GET /~cczdao HTTP/1.0" 301 336 128.243.101.85 - - [14/Dec/1999:13:41:54 +0000] "GET /~cczdao/ HTTP/1.0" 200 894 128.243.101.85 - - [14/Dec/1999:13:42:00 +0000] "GET /~cczdao/php/ HTTP/1.0" 200 1897 128.243.101.85 - - [14/Dec/1999:13:42:05 +0000] "GET /~cczdao/php/pgselect.php3 HTTP/1.0" 200 986 128.243.101.85 - - [14/Dec/1999:13:42:16 +0000] "GET /~cczdao/php/pginsert.php3 HTTP/1.0" 200 142 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /~cczdao/php/ HTTP/1.0" 200 1897 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/back.gif HTTP/1.0" 200 216 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/blank.gif HTTP/1.0" 200 148 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/text.gif HTTP/1.0" 200 229 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/text.gif HTTP/1.0" 200 229 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:34 +0000] "GET /icons/unknown.gif HTTP/1.0" 200 245 128.243.40.194 - - [14/Dec/1999:13:42:39 +0000] "GET /~cczdao/php/pgselect.php3 HTTP/1.0" 200 1020 128.243.40.194 - - [14/Dec/1999:13:42:40 +0000] "GET /~cczdao/php/pgselect.php3 HTTP/1.0" 200 1020 128.243.40.194 - - [14/Dec/1999:13:42:48 +0000] "GET /~cczdao/php/pginsert.php3 HTTP/1.0" 200 142 128.243.40.194 - - [14/Dec/1999:13:42:48 +0000] "GET /~cczdao/php/pginsert.php3 HTTP/1.0" 200 142 I've even run it while watching with a packet sniffer ("snoop" on Solaris), and can see the host running the browser sending two successive GETs for the pginsert.php3 page, with the host running Apache just sending back for each one a "200 OK" response and the page. No redirects or any other responses from Apache. I guess it may be a Netscape/Apache/HTTP issue, but it's really affecting my use of Postgres via PHP, and using the database that way is the reason I installed it. As I said, all database access via psql works fine, and all the standard regression tests were passed, so I'm confident of the Postgres installation. > Also, I am curious as to why you are not using > the nifty persistent connection pg_pconnect function? To keep things simple! If I'm right in thinking that pconnect is used to improve efficiency, it shouldn't make any difference whether pg_connect or pg_pconnect is used? Any ideas of where I should dig deeper to get to the bottom of this? thanks in advance David ---------------------- pginsert.php3 ------------- <?phpheader("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");header("Cache-control: no-cache, must-revalidate");header("Pragma:no-cache"); ?> <HTML> <HEAD> <TITLE>pgsql insertion test</TITLE> </HEAD> <BODY BACKGROUND="#FFFFFF"> <H1>pgsql insertion test</H1> <HR> <?php$conn = pg_connect("", "", "", "","cczdao");$result = pg_exec($conn, "insert into mytest values (444, 'some text')");pg_close($conn); ?> <HR> </BODY> </HTML>