Hi
čt 15. 6. 2023 v 10:32 odesílatel James Pang (chaolpan)
napsal:
>Thanks a lot, we use orafce 3.17, and there some varchar2 columns and
> function indexes depends on oracle.substr too. Is it ok to upgrade to
> orafce version 4.4 by “alter extension update to ‘4.4’? it’s online to do
> that ?
>
I didn't release 4.4, but it is available on github. Orafce supports
online upgrades
Hot fix can be execution of
https://github.com/orafce/orafce/blob/master/orafce--4.3--4.4.sql file
Regards
Pavel
>
> Thanks,
>
>
>
> James
>
>
>
> *From:* Pavel Stehule
> *Sent:* Tuesday, June 13, 2023 11:01 PM
> *To:* Tom Lane
> *Cc:* James Pang (chaolpan) ;
> [email protected]
> *Subject:* Re: Postgresql equal join on function with columns not use
> index
>
>
>
>
>
>
>
> út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule
> napsal:
>
>
>
>
>
> út 13. 6. 2023 v 15:50 odesílatel Tom Lane napsal:
>
> "James Pang (chaolpan)" 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
>
>
