phppostgresqlpdo

Why is an intarray operator not available when the query is called using PDO?


PostgreSQL version 14

I have a query behaving differently when run from pgAdmin than it does when run from PHP.

SELECT * FROM UNNEST('{1,2,3,4,5}'::integer[] - '{2,4}'::integer[])

When I run this from pgAdmin, it works fine. When I pass it in through PHP using PDO, I get ERROR: operator does not exist: integer[] - integer[].

This array operator is provided by the intarray module. CREATE EXTENSION intarray tells me that the extension is already installed. It's like the intarray module isn't available to the PHP process for some reason.

Can anyone point me in the right direction re what might be going on here?


Solution

  • As per @Adrian Klaver's comment, it's good to first confirm your PHP app and the client you were running the tests with, both connect to one and the same cluster and on it, the same database that does have intarray installed. Multiple instances running on the same host or behind tunnels set up on it, typos in database name or port number could all obfuscate the fact that you're dealing with distinct, mismatched DBs.

    Since you could confirm that's not the case, my bet would be that intarray is installed, just into a namespace that's not shown in your search_path/current_schemas(true) when you run your tests from the PHP app.
    You can check where it is: demo at db<>fiddle

    create schema some_other_schema;
    create extension intarray schema some_other_schema;
    select extname,extnamespace::regnamespace from pg_extension;
    
    extname extnamespace
    plpgsql pg_catalog
    intarray some_other_schema
    select e.extnamespace::regnamespace
         , p.oprnamespace::regnamespace
         , e.extname
         , p.oid::regoperator
         , p.oprname
         , p.oprcode::regprocedure
    from pg_catalog.pg_operator as p
    join pg_catalog.pg_depend    as d on (d.objid    = p.oid)
    join pg_catalog.pg_extension as e on (d.refobjid = e.oid)
    where e.extname='intarray'
      and p.oprname='-';
    
    extnamespace oprnamespace extname oid oprname oprcode
    some_other_schema some_other_schema intarray some_other_schema.-(integer[],integer) - some_other_schema.intarray_del_elem(integer[],integer)
    some_other_schema some_other_schema intarray some_other_schema.-(integer[],integer[]) - some_other_schema.intset_subtract(integer[],integer[])

    Once you're sure where it is, add schema qualification of the operator with operator():

    select array[1,2] operator(some_other_schema.-) array[2,3];
    

    Or add the schema to search_path:

    select set_config( 'search_path'
                      ,'some_other_schema, '
                       ||current_setting('search_path')
                      ,false);
    

    Which lets you skip the operator's schema qualification:

    select array[1,2] - array[2,3];
    

    It's worth pointing out that contrary to what the extension name would suggest, intarray's int[] - int[] subtracts sets, not arrays. Both operands will be sorted and deduplicated prior to the subtraction. It's a bit clearer when you check that the - variant subtracting whole other int[] is called intset_subtract while the one subtracting a single element is called intarray_del_elem and it doesn't do the silent uniq(sort()).