Partition pruning based on check constraint not working as expected

Why is the table "events_201504" included in the query plan below? Based on my query and the check constraint on that table I would expect the query planner to be able to prune it entirely:

database=# \d events_201504
                                   Table "public.events_201504"
    Column     |            Type             |                           Modifiers
---------------+-----------------------------+---------------------------------------------------------------
 id            | bigint                      | not null default nextval('events_id_seq'::regclass)
 created_at    | timestamp without time zone |
Indexes:
    "events_201504_pkey" PRIMARY KEY, btree (id)
    "events_201504_created_at" btree (created_at)
Check constraints:
    "events_201504_created_at_check" CHECK (created_at >= '2015-04-01 00:00:00'::timestamp without time zone AND created_at <= '2015-04-30 23:59:59.999999'::timestamp without time zone)
Inherits: events

Time and configuration:

database=# select now();
              now
-------------------------------
 2015-05-25 16:49:20.037815-05

database=# show constraint_exclusion;
 constraint_exclusion
----------------------
 on

The query plan:

database=# explain select count(1) from events where created_at > now() - '1 hour'::interval;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3479.86..3479.87 rows=1 width=0)
   ->  Append  (cost=0.00..3327.90 rows=60784 width=0)
         ->  Seq Scan on events  (cost=0.00..0.00 rows=1 width=0)
               Filter: (created_at > (now() - '01:00:00'::interval))
         ->  Index Only Scan using events_201504_created_at on events_201504  (cost=0.57..4.59 rows=1 width=0)
               Index Cond: (created_at > (now() - '01:00:00'::interval))
         ->  Index Only Scan using events_201505_created_at on events_201505  (cost=0.57..3245.29 rows=60765 width=0)
               Index Cond: (created_at > (now() - '01:00:00'::interval))

Your column created_at is of type timestamp without time zone.

But now() returns timestamp with time zone. The expression now() - '1 hour'::interval is being coerced to timestamp [without time zone], which carries two problems:

1. You did not ask for this, but the expression is unreliable. Its result depends on the timezone setting of the current session. See:

• Ignoring time zones for Postgres timestamps

To make the expression clear, you could use:

now() AT TIME ZONE 'Europe/London' -- your time zone here

Or just (read the manual here):

LOCALTIMESTAMP  -- explicitly take the local time

I would consider working with timestamptz instead.
Neither solves your second problem:

2. Answer to your question. Constraint exclusion does not work. The manual:

The following caveats apply to constraint exclusion:

• [...]
• Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.

Bold emphasis mine.

now() is the Postgres implementation of CURRENT_TIMESTAMP. As you can see in the system catalog, it is only STABLE, not IMMUTABLE:

SELECT proname, provolatile FROM pg_proc WHERE proname = 'now';

proname | provolatile
--------+------------
now     | s              -- meaning: STABLE

Solutions

1. You can overcome the limitation by providing a constant in the WHERE condition (which is always "immutable"):

SELECT count(*) FROM events
WHERE created_at > '2015-05-25 15:49:20.037815'::timestamp;  -- from your example

2. Or by "faking" an immutable function:

CREATE FUNCTION f_now_immutable()
  RETURNS timestamp
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT now() AT TIME ZONE 'UTC';  -- your time zone here
$func$;

And then:

SELECT count(*) FROM events
WHERE created_at > f_now_immutable() - interval '1 hour';

Be careful how you use this though: while now() is STABLE (does not change for the duration of a transaction), it does change between transactions, so take care not to use this in prepared statements (except as parameter value) or indexes or anything where it might bite you.

3. Or you can add seemingly redundant constant WHERE clauses to your current query that match the constraint on your partition:

SELECT count(*)
FROM   events
WHERE  created_at > now() - '1 hour'::interval
AND    created_at >= '2015-04-01 00:00:00'::timestamp
AND    created_at <= '2015-04-30 23:59:59.999999'::timestamp;

Just make sure yourself that now() - '1 hour'::interval falls into the right partition or you get no results, obviously.

Aside: I would rather use this expression in CHECK constraints and query. Easier to handle and does the same:

       created_at >= '2015-04-01'::timestamp
AND    created_at <  '2015-05-01'::timestamp