I had a table with a bigserial PK, one character varying FK and a bigint FK and a unique constraint on these FKs, a character varying NOT NULL and two nullable smallints.
All of this was running in a VM with Ubuntu 14.04.2 LTS, PHP 5.5.9 and PostgreSQL 9.3 and all other packages from the distribution.
I did
$pdo->query("SELECT * FROM table")->fetchAll(PDO::FETCH_ASSOC)
and
$r = pg_query($db, "SELECT * FROM table");
pg_fetch_all($r)
The latter returned all rows while the former returned all rows except for one.
I tried to select that very row as well, and while the pg_*() functions worked as expected, PDO did not return any row.
To make it even stranger, the discrepancy only emerged in PHP-FPM with lighttpd 1.4.33, but not when run with PHP CLI in a shell.
How can that be? Did anyone else encounter a similar situation? And how can I be sure that PDO will not fail me again?
I even tried rebooting the VM and restarted PostgreSQL, but the results did not change.
Sadly, I do not have a backup of this stuff because I had to get things working ASAP, so I deleted that mysterious row and replaced it with a new one, identical in all columns except for the PK.
The new row was returned properly by both.
Turns out, PostgreSQL may yield confusing and inconsistent results when its index gets corrupted.
In this case, just recreate the index and check if it fixes your problem.