Re: BUG #6662: Database do not push condition to subquery, test case for bug 6658

Hi,

Thank you very much for answer.
Explicit casting resolved an issue.

Just single question:
if anytextcat() and textanycat() are marked volatile,
why the database allows create index on supposedly to be volatile
expression:
create index test_val_special on test((val || ''));
?

P

On Wed, May 23, 2012 at 11:46 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> maxim(dot)boguk(at)gmail(dot)com writes:
> > I managed create simple self-contained test case for 6658.
>
> This works fine in HEAD. The reason it doesn't work fine in 9.1 (or
> 9.0) is that in those branches, anytextcat() and textanycat() are marked
> volatile, for reasons that were good at the time but were superseded by
> later policy changes. So you have a subselect containing a volatile
> output expression, which prevents flattening of the subselect, so the
> restriction clause doesn't get pushed down to where it could be used
> with the index. For some history see
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c82d931dd180965a9a0c06acc764404f91de8170
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=aab353a60b95aadc00f81da0c6d99bde696c4b75
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3db6524fe63f0598dcb2b307bb422bc126f2b15d
>
> If this is a big problem for you, you could safely adjust the
> provolatile marking of those functions by hand in 9.1. It might be
> better just to include an explicit cast to text, though, instead of
> relying on the assumption that the system will let you concat an integer
> directly to a text string.
>
> regards, tom lane
>

--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."