> From: Michael Lewis <mle...@entrata.com> > Sent: Wednesday, September 8, 2021 13:40 > To: Tom Lane <t...@sss.pgh.pa.us> > Cc: l...@laurent-hasson.com; pgsql-performa...@postgresql.org > Subject: Re: Better performance no-throw conversion? > > On Wed, Sep 8, 2021 at 11:33 AM Tom Lane <mailto:t...@sss.pgh.pa.us> wrote: > "mailto:l...@laurent-hasson.com"; <mailto:l...@laurent-hasson.com> writes: > > Some databases such as SQLServer (try_cast) or BigQuery (safe.cast) offer > > not-throw conversion. > > ... > > I couldn't find a reference to such capabilities in Postgres and wondered > > if I missed it, and if not, is there any plan to add such a feature? > > There is not anybody working on that AFAIK. It seems like it'd have > to be done on a case-by-case basis, which makes it awfully tedious. > > Do you just mean a separate function for each data type? I use similar > functions (without a default value though) to ensure that values extracted > from jsonb keys can be used as needed. Sanitizing the data on input is a long > term goal, but not possible immediately. > > Is there any documentation on the impact of many many exception blocks? That > is, if such a cast function is used on a dataset of 1 million rows, what > overhead does that exception incur? Is it only when there is an exception or > is it on every row? > >
Hello Michael, There was a recent thread (Big Performance drop of Exceptions in UDFs between V11.2 and 13.4) that I started a few weeks back where it was identified that the exception block in the function I posted would cause a rough 3x-5x performance overhead for exception handling and was as expected. I identified a separate issue with the performance plummeting 100x on certain Windows builds, but that's a separate issue. Thank you, Laurent.
