Thread: PostgresQL or PHP bug?

Sorry if this is a known issue/problem but I couldn't find any related
bugs or postings ... also sorry for the length of the post but I am
pretty much at my wit's end ...

I've been tearing my hair out trying to find out if the problem I am
seeing is a bug in my program, PHP, or PostgresQl. After all the
hair pulling I am pretty sure it is not in my program (but I *could* be
wrong). So that leaves me with PostgresQl or PHP.

I'm inclined to think it is a PHP bug but since I don't know much about
PostgresQl it could also be my understanding of how the DB works.

Basically I am inserting data into my DB using PHP to read the data from
a file and then insert it into the DB. Before inserting an item into the
DB I check to see if it is already in the DB. (there are a few duplicate
entries in the data file I am using) The data file contains 14562 items.
Of these there are 15 duplicates.

The problem I am seeing is that when I run my PHP program I have it
print out how many times the data it wanted to insert was already in DB
(a duplicate entry from the data file presumably). I have run the
program four times and each time I get a different result!

But the total number of inserted items is always the same ...

Each are the results after four runs:

Duplicate Items   Total DB inserts
5099            14285
5000            14285
5125            14285
5098            14285

The program looks like this:

(no transactions are explicitly used in any of the code)

1- prepare some accessory tables
2- go through the data file line by line (each line is one row)
  2-1 extract the column data from the line (one datum is a product id)
  2-1 check if there is already a row with this product id in the DB
   2-1-1 IF there is log it as a duplicate item and move onto next line
  2-2 add the data into various tables (about 6)

Why would I get differing results for the number of duplicate items? I'm
guessing it has something to do with the psql backend and/or how PHP
connects to it.

Why do I get different results on each of the four runs??

I ran another program that doesn't do step 2-2 and it worked fine. At
every run the number of duplicate items is 15.

It's not a problem right now because in the end all the data gets
inserted but it is making me worried about the "correctness" of the
results returned to me by the DB.

Can someone give me a hint as to how to pin down where this problem is
coming from?

I'm including the PHP code for steps 2-1 as it is quite short:

if (product_id_exists($prod_id)) {
   return 3; //this will cause the prod_id to be logged as a duplicate
}

function sql_query($sql) {

   $conn = pg_connect("dbname=JC user=postgres");
   $res  = pg_exec($conn, $sql);

   if (!$res) {
     echo "CONNECTION: could not execute query ($sql)<br>";
     die;
   }
   else {return $res;}
}


Thanks!

Jc

I don't see anything obvious but have a look at the rows it is considering
duplicates. Are they the same list each time? By the way, it looks like
you're connect()ing once for each query you are executing. You should only
connect once at the beginning of the program.

Hope this helps,

On Sun, Sep 08, 2002 at 12:48:20AM +0900, Jean-Christian Imbeault wrote:
> The problem I am seeing is that when I run my PHP program I have it
> print out how many times the data it wanted to insert was already in DB
> (a duplicate entry from the data file presumably). I have run the
> program four times and each time I get a different result!
>
> But the total number of inserted items is always the same ...
>
> Each are the results after four runs:
>
> Duplicate Items   Total DB inserts
> 5099            14285
> 5000            14285
> 5125            14285
> 5098            14285
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Martijn van Oosterhout wrote:

 >I don't see anything obvious but have a look at the rows it is
 >considering duplicates. Are they the same list each time? By the way,
 >it looks like you're connect()ing once for each query you are
 >executing. You should only connect once at the beginning of the
 >program.

Unfortunately I didn't check to see if it always outputted the same
items as being duplicates. I would think not but I'll try and check it out.

As for connect()ing once every query, I was under the impression that
PHP would only really connect() the first time, and on subsequent calls
to connect(), if the connect parameters are the same, would just return
the same backend connection.

The connection gets closed only (and automatically) once the PHP script
ends.

But that is only my *understanding* of PHP and connect() I might be
wrong on the implementation might be buggy (very possible).

I'll try connect()ing just once and also seeing if the duplicate items
are the same and report back.

Thanks for having a look, I really appreciate it!

Jc