Re: Partitioned tables constraint_exclusion

Weslee Bilodeau wrote:
> Mainly its because the value comes from a reporting system that has
> minimal brains, it passes values it gets from the user directly into a
> query.
>
> IE, they enter '1 month', which I use to populate the interval value,
> "ts > ( NOW() - $VALUE )"
>
> But, in the example I did a "timestamp - interval", the exact date, not
> NOW() - Still didn't work.
>
> I'm guessing anything that has to think, math, etc is not valid for
> constrain_exclusion?
>
> Its not in the docs anywhere, so trying to isolate what can and can't be
> done.

This works -

CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE
STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ;

SELECT count(*) FROM master WHERE var_ts > now_interval( '1 month' );

This doesn't work -

SELECT count(*) FROM master WHERE var_ts > ( NOW() - '1 month'::interval );

This works for me, as the reporting system I know doesn't change
timezones, and function cache doesn't last longer then the current select?

But, its basically the exact same logic in both cases?

Weslee