duanfu1873 2012-09-04 22:41
浏览 61


I have a PHP script which inserts a row into a Postgres DB. It works fine on my test server (Postgres 9.1) but fails on my new shared host (Postgres 8.4).

$query = "INSERT INTO " . $table . " VALUES (DEFAULT, $1, $2, CURRENT_TIMESTAMP::TIMESTAMP(0), $3, $4, ST_Point($4, $3))";
$result = pg_send_query_params($dbconn, $query, array($is_happy, $rate, $lat, $lon));

The $result is always TRUE but no row is inserted.

I also tried:

$result = pg_get_result($dbconn);

But experienced the same behaviour.

Following @CraigRinger's transaction suggestion below I also tried both:

$query = "BEGIN;INSERT INTO " . $table . " VALUES (DEFAULT, $1, $2, CURRENT_TIMESTAMP::TIMESTAMP(0), $3, $4, ST_Point($4, $3));COMMIT;";
$result = pg_send_query_params($dbconn, $query, array($is_happy, $rate, $lat, $lon));


pg_send_query($dbconn, "BEGIN;");
$query = "INSERT INTO " . $table . " VALUES (DEFAULT, $1, $2, CURRENT_TIMESTAMP::TIMESTAMP(0), $3, $4, ST_Point($4, $3));";
$result = pg_send_query_params($dbconn, $query, array($is_happy, $rate, $lat, $lon));
pg_send_query($dbconn, "COMMIT;");

And still got the same behaviour.

If I run the INSERT query in phpPgAdmin a row is inserted. I am also able to get the results from this query in a PHP script:

"SELECT relid FROM pg_stat_user_tables"

What does this mean when the INSERT query result is true but no row is inserted? What could be the problem and how might I approach resolving this?


I can INSERT rows using phpPgAdmin and then SELECT these rows from a PHP script.

Here is a full PHP script that fails (it is stripped of POST gathering and validation that exists in my full script). The rest.php script simply holds connection vars and a response function. This rest.php script works with my SELECT script:

require "KLogger.php";
require "rest.php";

ini_set("error_reporting", E_ALL ^ E_NOTICE);
ini_set("display_errors", 0);
ini_set("log_errors", 1);

$log   = KLogger::instance(dirname(__FILE__), KLogger::DEBUG);
$log_id = "AM".time();
$log->logInfo($log_id . " *** " . $_SERVER['REMOTE_ADDR']);

$lat = '51.510199';
$lon = '-0.129654';
$rate = '10';
$is_happy = 't';

$dbconn = pg_connect("host=" . $host . " dbname=" . $db . " user=" . $user . " password=" . $pw);
    $log->logInfo($log_id . " No connection: " . pg_last_error());
    sendResponse(500, "Internal Server Error");
    $query = "INSERT INTO " . $table . " VALUES (DEFAULT, $1, $2, CURRENT_TIMESTAMP::TIMESTAMP(0), $3, $4, ST_Point($4, $3));";
    $result = pg_send_query_params($dbconn, $query, array($is_happy, $rate, $lat, $lon));
        $log->logInfo($log_id . " No Result: " . pg_last_error());
        sendResponse(500, "Internal Server Error");
        $log->logInfo($log_id . " sendResponse(200)");
        sendResponse(200, "OK");
  • 写回答

1条回答 默认 最新

  • dsb53973 2012-09-04 22:53

    The most common cause for this kind of issue is an open, uncomitted transaction.

    If you BEGIN (or do so implicitly by turning autocommit off in your database driver) then do some work, only the transaction that did the work can see the changes until it COMMITs.

    If that is the case then an immediate follow-up query from within the same script using the same connection will see the changed row, but nothing else will.

    You can also check for this by enabling:

    log_statement = 'all'
    log_line_prefix = 'db=%d pid=%p vtxid=%v txid=%x'

    and finding mismatched transactions by looking for virtual transaction IDs (vtxid) with a BEGIN and no matching COMMIT.

    If you can't modify postgresql.conf because it's shared hosting you should be able to ALTER USER myuser SET log_statement = 'all'; or ALTER DATABASE mydatabase SET log_statement = 'all';. You can't set log_line_prefix outside postgresql.conf but your host might have a reasonable setting already.

    Another possibility - and one that might be explained by the version difference - is that you're triggering an error later in the transaction by using a feature only available in 9.1, and you aren't detecting that error. Your earlier INSERT succeeds, but the whole transaction then gets rolled back when the later statement fails. Again, examine the server logs to see.

    Another common cause is that you're connecting to a different database with the script than the one you connect to when you test to see if it's inserted. Try SELECTing the row you expect to have been inserted from the script after you insert it. It's also worth creating a dummy table via your admin tool (phpPgAdmin in your case, it seems) and then testing to see whether the script can see the dummy table.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



    • ¥20 win10商店接入问题
    • ¥15 java 这种树形框吗
    • ¥40 找同学帮敲Python代码
    • ¥15 MYSQL 订单的商品明细重复计算问题
    • ¥15 微信实时共享位置修改
    • ¥100 TG的session协议号转成直登号号后客户端登录几分钟后自动退出设备
    • ¥50 共模反馈回路的小信号增益
    • ¥15 arduino ssd1306函数与tone函数放歌代码不兼容问题
    • ¥70 0.96版本hbase的row_key里含有双引号,无法deleteall
    • ¥15 诊断性META分析合并效能的检验