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@sss.pgh.pa.us> wrote:

> maxim.boguk@gmail.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=3Dpostgresql.git&a=3Dcommitdiff&h=3Dc=
82d931dd180965a9a0c06acc764404f91de8170
>
> http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3Da=
ab353a60b95aadc00f81da0c6d99bde696c4b75
>
> http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3D3=
db6524fe63f0598dcb2b307bb422bc126f2b15d
>
> 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
>



--=20
Maxim Boguk
Senior Postgresql DBA.

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

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com
=D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: 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."