Re: Allow use of stable functions with constraint exclusion

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Marshall, Steve" <smarshall(at)wsi(dot)com> writes:
> > I have developed a small patch to optimizer/util/plancat.c that
> > eliminates one of hte caveats associated with constraint exclusions,
> > namely the inability to avoid searching tables based on the results of
> > stable functions.
>
> Do you not understand why this is completely unsafe?

I think the proposal itself is very useful, because time-based
partitioning is commonly used and functions like now() or
CURRENT_TIMESTAMP are marked as stable.

I'm not clear why the optimization is unsafe. I'm confused to read the
definition of stable functions in our documentation. Which is required
for stable functions 'stable in a single table scan' or 'stable in a
SQL statements' ? If the latter definition is true, can we use them
in constraint exclusions?

| STABLE indicates that the function cannot modify the database, and
| that within a single table scan it will consistently return the same
| result for the same argument values, but that its result could change
| across SQL statements.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center