Problems with PDO for PostgreSQL on 32-bit machine

Size of integer in PHP depends on machine, it has 32 bits on 32-bit architectures, and 64 on 64-bit architectures.

On 32-bit machines PDO for PostgreSQL always convert bigint numbers returned by server to string. Never cast to integer even if value of bigint would fit in 32-bit signed integer.

Type bigint is returned for example by COUNT and SUM functions.

On 64-bit machines there is no such problem because PHP integer is the same as bigint.

Short story about PostgreSQL SUM function

Here is a simple PostgreSQL type:

CREATE TYPE foo_t AS (
 id    integer,
 total bigint
);

and a simple query wrapped in stored procedure:

CREATE FUNCTION group_foo()
 RETURNS SETOF foo_t
 LANGUAGE "SQL"
AS $$
 SELECT id, SUM(some_column) FROM some_table GROUP BY id;
$$;

Now, we want to sum everything:

CREATE FUNCTION total_foo()
 RETURNS bigint -- same as foo_t.total
 LANGUAGE "SQL"
AS $$
 SELECT SUM(total) FROM group_foo();
$$;

And we have an error about type inconsistency!

This is caused by SUM function -- in PostgreSQL there are many variants of this function, as the db engine supports function name overriding (sounds familiar for C++ guys). There are following variants in PostgreSQL 9.1:

$ \df sum
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type 
------------+------+------------------+---------------------+------
 pg_catalog | sum  | numeric          | bigint              | agg
 pg_catalog | sum  | double precision | double precision    | agg
 pg_catalog | sum  | bigint           | integer             | agg
 pg_catalog | sum  | interval         | interval            | agg
 pg_catalog | sum  | money            | money               | agg
 pg_catalog | sum  | numeric          | numeric             | agg
 pg_catalog | sum  | real             | real                | agg
 pg_catalog | sum  | bigint           | smallint            | agg

Smaller types are promoted: from integer we get bigint, from bigint we get numeric, and so on.

SQL surprise

Secret Hackers Rule #11: Hackers read manuals.

Recently I've discovered that PostgreSQL supports setting NULL on column referenced to another table (i.e. foreign constraint) before deleting a row:

CREATE TABLE foo (
 id integer PRIMARY KEY
);

CREATE TABLE bar (
 id integer PRIMARY KEY,
 foo_id integer REFERENCES foo ON DELETE SET NULL
);

INSERT INTO foo (id) VALUES (1);
INSERT INTO bar (id, foo_id) VALUES (1, 1);
DELETE FROM foo;

Without ON DELETE SET NULL server reports error that key foo.id = 1 is still referenced in table bar.

Speeding up LIKE '%text%' queries

Recently I did some experminets with n-grams indexes used to speed-up full text search in a plain SQL database. The results are really promising.

PostgrSQL: printf in PL/pgSQL

PostgreSQL wiki has entry about sprintf - is is quite simple approach (and isn't marked as immutable), the main drawback is iterating over all chars of format string. Here is a version use strpos to locate % in format string, and it's faster around 2 times:

CREATE OR REPLACE FUNCTION printf2(fmt text, variadic args anyarray) RETURNS text
LANGUAGE plpgsql IMMUTABLE AS $$
   DECLARE
      argcnt  int  := 1;
      head    text := '';     -- result
      tail    text := fmt;    -- unprocessed part
      k       int;
   BEGIN
      LOOP
         k := strpos(tail, '%');
         IF k = 0 THEN
            -- no more '%'
            head := head || tail;
            EXIT;
         ELSE
            IF substring(tail, k+1, 1) = '%' THEN
               -- escape sequence '%%'
               head := head || substring(tail, 1, k);
               tail := substring(tail, k+2);
            ELSE
               -- insert argument
               head := head || substring(tail, 1, k-1) || COALESCE(args[argcnt]::text, '');
               tail := substring(tail, k+1);
               argcnt := argcnt + 1;
            END IF;
         END IF;
      END LOOP;
   RETURN head;
END;
$$;

PostgreSQL: get selected rows with given order

Suppose that database stores some kind of dictionary and user picks some items, but wants to keep order. For example dictionary has entries with id=0..10, and user picked 9, 2, 4 and 0. This simple query does the job (query splitted):

foo = SELECT (ARRAY[9,2,4,0])[i] AS index, i AS ord FROM  generate_series(1, 4) AS i;
SELECT * FROM dictionary INNER JOIN (foo) ON dictionary.id=foo.index ORDER BY foo.ord