Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The scenario

Two systems(not server) running PHP and PostgreSQL with the following versions

  • Fedora 15:

    PHP

    PHP 5.3.13 (cli) (built: May 9 2012 14:38:35)
    Copyright (c) 1997-2012 The PHP Group
    Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
    pdo_pgsql
    PostgreSQL(libpq) Version 9.0.7
    Module version 1.0.2


    PostgreSQL

    PostgreSQL 9.1.4
    With CITEXT extension enabled.

  • ArchLinux:

    PHP

    PHP 5.4.6 (cli) (built: Aug 16 2012 12:50:09)
    Copyright (c) 1997-2012 The PHP Group
    Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
    pdo_pgsql
    PostgreSQL(libpq) Version 9.1.4
    Module version 1.0.2


    PostgreSQL

    PostgreSQL 9.1.4
    With CITEXT extension enabled.


when a simple query such as

select column1 from schema1.table1 where column1= ? 

Where column1 is of the type CITEXT, is executed through PHP PDO

  • On Fedora with PHP 5.3.13,libpq 9.0.7, the query performs as expected with CITEXT(a case insensitive search occurs).
  • On ArchLinux with PHP 5.4.6,libpq 9.1.4, the query does not perform as expected with CITEXT(a case sensitive search occurs).

I am guessing the newer version of PHP PDO libs are doing something similar to this:

select column1 from schema1.table1 where column1= 'value'::text;

during binding.

  • Am I right?
  • Is there a workaround? Otherwise, the use of CITEXT as a column data type to gain the advantage of case insensitive search is useless when using newer versions of PDO.

Update

After turning statement level logging on, on ArchLinux with PHP 5.4.6,libpq 9.1.4 :

LOG:  execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL:  parameters: $1 = 'value'
LOG:  statement: DEALLOCATE pdo_stmt_00000001

where the actual value of the column column1 is VALUE.

Still comes back with 0 elements.

When the statement

select column1 from schema1.table1 where column1 = 'value';

is executed directly on the PSQL prompt comes back with a single row.

 column1  
---------
  VALUE
 (1 row)

So, the type casting does not take place! I still am not able to understand the behaviour of PDO/postgresql.


Update 2012-08-27 16:15:43.669142+00 (UMT + 0)

After trying to directly execute a query without preparing a statement.

Here is the code that was used to test :

try {
    $db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
    $retval=$db->query($sql);
    foreach ($retval as $row) {
        print $row['uname'] . '<br>';
    }
}catch (PDOException $PDOerr) {
    echo 'An error occured : <br>';
    var_dump($PDOerr);
    exit;
    //some thing went wrong while performing the action on db.
    }

I get the error :

object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...

I do not understand why citext is not getting detected! When the statement is executed directly on the PSQL prompt, everything works fine as mentioned above.

share|improve this question
    
Your updates make me think this is a permissions issue. Are you using the same user via PHP as via psql? –  Jack Douglas Aug 27 '12 at 17:52
    
Incidentally have you tried select column1 from schema1.table1 where column1= 'value'::text; from psql? I think you will find it casts the value right back to citext implicitly. –  Jack Douglas Aug 27 '12 at 17:53
    
@JackDouglas both the installations have the same user and same grant permissions –  ThinkingMonkey Aug 27 '12 at 17:53
    
@JackDouglas Yes, I have.. it works as excepted. CITEXT does gets recognized as a valid datatype. And the result is a single row. –  ThinkingMonkey Aug 27 '12 at 17:55
    
can you narrow the problem down, eg by changing your query to select column1 from schema1.table1 order by column1 limit 1; and comparing? –  Jack Douglas Aug 27 '12 at 17:58
show 1 more comment

1 Answer

up vote 3 down vote accepted

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.

share|improve this answer
    
It was a search path problem for the user I was trying to login as. I am guessing I had created the user when I had done a session-wise set search_path to some other schema and instead of the default '"$user",public' it was set to the other schema. The user did not have any access to public schema at all. Thanks for pointing me in the right direction. –  ThinkingMonkey Aug 27 '12 at 20:21
    
BTW, used ALTER ROLE user SET search_path TO "$user",public; to set the search path. Although, $user is useless as I do not have any schema named after the current users. –  ThinkingMonkey Aug 27 '12 at 20:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.