Skip site navigation (1) Skip section navigation (2)

Re: Partitioned tables constraint_exclusion

From: Weslee Bilodeau <weslee(dot)bilodeau(at)hypermediasystems(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Partitioned tables constraint_exclusion
Date: 2007-03-26 20:48:57
Message-ID: 460831B9.9090503@hypermediasystems.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-03-26 21:47:39
Subject: Re: BSD advertizing clause in some files
Previous:From: Joris DobbelsteenDate: 2007-03-26 20:47:32
Subject: Re: Guarenteeing complex referencial integrity through custom triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group