Hi čt 15. 6. 2023 v 10:32 odesílatel James Pang (chaolpan) <chaol...@cisco.com> 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 <pavel.steh...@gmail.com> > *Sent:* Tuesday, June 13, 2023 11:01 PM > *To:* Tom Lane <t...@sss.pgh.pa.us> > *Cc:* James Pang (chaolpan) <chaol...@cisco.com>; > pgsql-performance@lists.postgresql.org > *Subject:* Re: Postgresql equal join on function with columns not use > index > > > > > > > > út 13. 6. 2023 v 16:17 odesílatel Pavel Stehule <pavel.steh...@gmail.com> > napsal: > > > > > > út 13. 6. 2023 v 15:50 odesílatel Tom Lane <t...@sss.pgh.pa.us> napsal: > > "James Pang (chaolpan)" <chaol...@cisco.com> 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 > >
