Re: Postgresql equal join on function with columns not use index

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule <pavel.stehule@xxxxxxxxx> napsal:

út 13. 6. 2023 v 15:50 odesílatel Tom Lane <tgl@xxxxxxxxxxxxx> napsal:

"James Pang (chaolpan)" <chaolpan@xxxxxxxxx> writes:
>     Looks like it's the function "regexp_replace" volatile and restrict=false make the difference,  we have our application role with default search_path=oracle,$user,public,pg_catalog.   
>      =#    select oid,proname,pronamespace::regnamespace,prosecdef,proisstrict,provolatile from pg_proc where proname='regexp_replace' order by oid;
>   oid  |    proname     | pronamespace | prosecdef | proisstrict | provolatile
> -------+----------------+--------------+-----------+-------------+-------------
>   2284 | regexp_replace | pg_catalog   | f         | t           | i
>   2285 | regexp_replace | pg_catalog   | f         | t           | i
>  17095 | regexp_replace | oracle       | f         | f           | v
>  17096 | regexp_replace | oracle       | f         | f           | v
>  17097 | regexp_replace | oracle       | f         | f           | v
>  17098 | regexp_replace | oracle       | f         | f           | v

Why in the world are the oracle ones marked volatile?  That's what's
preventing them from being used in index quals.

It looks like orafce issue

I'll fix it

should be fixed in orafce 4.4.

Regards

Pavel

 

Regards

Pavel

 

                        regards, tom lane