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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

čt 15. 6. 2023 v 10:32 odesílatel James Pang (chaolpan) <chaolpan@xxxxxxxxx> 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.stehule@xxxxxxxxx>
Sent: Tuesday, June 13, 2023 11:01 PM
To: Tom Lane <tgl@xxxxxxxxxxxxx>
Cc: James Pang (chaolpan) <chaolpan@xxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Postgresql equal join on function with columns not use index

 

 

 

ú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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux