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 ?
Thanks, James From: Pavel Stehule <[email protected]> Sent: Tuesday, June 13, 2023 11:01 PM To: Tom Lane <[email protected]> Cc: James Pang (chaolpan) <[email protected]>; [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 <[email protected]<mailto:[email protected]>> napsal: út 13. 6. 2023 v 15:50 odesílatel Tom Lane <[email protected]<mailto:[email protected]>> napsal: "James Pang (chaolpan)" <[email protected]<mailto:[email protected]>> 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
