php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #54863 pg_query_params not returning value from PostgreSQL rule
Submitted: 2011-05-19 16:29 UTC Modified: 2013-06-28 20:38 UTC
Votes:3
Avg. Score:4.0 ± 0.8
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (100.0%)
From: courts at siam dot org Assigned:
Status: Analyzed Package: PostgreSQL related
PHP Version: * OS: *
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2011-05-19 16:29 UTC] courts at siam dot org
Description:
------------
When using a PostgreSQL rule to return a value on an INSERT, pg_query's return is a resource that contains the correct information, while pg_query_params's return is empty/null.

Tested and confirmed on:
'Linux 2.6.18-194.17.1.el5 #1 SMP Mon Sep 20 07:12:06 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux' running PHP 5.3.5-1 [Note:  Production server - can't upgrade myself for testing]
and on 
'Linux 2.6.38-8-generic #42-Ubuntu SMP Mon Apr 11 03:31:50 UTC 2011 i686 i686 i386 GNU/Linux' virtual machine running PHP 5.3.5-1 and then also tested against 5.3.6 compiled from source.  


Test script:
---------------
Database setup:
    CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;
    CREATE SEQUENCE widget_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; 
    ALTER TABLE widget_id_seq OWNER TO postgres;
    CREATE TABLE widget ( id serial NOT NULL, widgetname character varying(20) NOT NULL, inserttime timestamp without time zone NOT NULL, CONSTRAINT widget_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE );
    ALTER TABLE widget OWNER TO postgres;
    CREATE OR REPLACE RULE get_widget_id_on_insert AS ON INSERT TO widget DO  SELECT currval('widget_id_seq'::text::regclass) AS id;

PHP test script:
<?php
    $db_connection = pg_connect("host=localhost dbname=test user=postgres");

    $good_query  = "INSERT INTO widget (widgetname, inserttime) VALUES ('Good Test Widget', NOW())";
    $good_result_ref = pg_query($db_connection, $good_query);
    $good_result_data =  pg_fetch_array($good_result_ref);
    echo 'Good result ID:' . $good_result_data['id'] . "\n";

    $bad_query  = 'INSERT INTO widget (widgetname, inserttime) VALUES ($1, NOW())';
    $bad_params = array('Bad Test Widget');
    $bad_result_ref = pg_query_params($db_connection, $bad_query, $bad_params);
    $bad_result_data =  pg_fetch_array($bad_result_ref);
    echo 'Bad result ID:' . $bad_result_data['id'] . "\n";

    pg_close($db_connection);
    exit;
?>

Expected result:
----------------
(Note:  The difference between this and the actual result is that "Bad result ID:" has an integer value (its id) accessible)

[justin@mybox ~]$ php rule_failure.php
Good result ID:1
Bad result ID:2
[justin@mybox ~]$ php rule_failure.php
Good result ID:3
Bad result ID:4
[justin@mybox ~]$ psql --dbname test --user postgres
psql (8.4.7)
Type "help" for help.

test=# SELECT * FROM widget;
 id |    widgetname    |         inserttime
----+------------------+----------------------------
  1 | Good Test Widget | 2011-05-18 15:57:22.182711
  2 | Bad Test Widget  | 2011-05-18 15:57:22.187533
  3 | Good Test Widget | 2011-05-18 15:57:26.16656
  4 | Bad Test Widget  | 2011-05-18 15:57:26.170966
(4 rows)

test=#


Actual result:
--------------
[justin@mybox ~]$ php rule_failure.php
Good result ID:1
Bad result ID:
[justin@mybox ~]$ php rule_failure.php
Good result ID:3
Bad result ID:
[justin@mybox ~]$ psql --dbname test --user postgres
psql (8.4.7)
Type "help" for help.

test=# SELECT * FROM widget;
 id |    widgetname    |         inserttime
----+------------------+----------------------------
  1 | Good Test Widget | 2011-05-18 15:57:22.182711
  2 | Bad Test Widget  | 2011-05-18 15:57:22.187533
  3 | Good Test Widget | 2011-05-18 15:57:26.16656
  4 | Bad Test Widget  | 2011-05-18 15:57:26.170966
(4 rows)

test=#


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-03-31 06:25 UTC] yohgaki@php.net
-Type: Bug +Type: Documentation Problem
 [2012-03-31 06:25 UTC] yohgaki@php.net
pg_query_params() calls libpq's PQqueryParams(). There aren't much we can do. You 
might want to report this to PostgreSQL group.

Changed to Documentation Problem.
 [2013-06-28 20:38 UTC] yohgaki@php.net
-Status: Open +Status: Analyzed -Operating System: Linux 2.6 +Operating System: * -PHP Version: 5.3.6 +PHP Version: *
 [2013-06-28 20:38 UTC] yohgaki@php.net
The behavior is the same, result resources were returned but it cannot retrieve 
data. 

Tested with PostgreSQL 9.2 and PHP 5.4/5.5
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Mon Mar 25 16:01:26 2019 UTC