From: | "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> |
---|---|
To: | Michael Lewis <mlewis(at)entrata(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE: Better performance no-throw conversion? |
Date: | 2021-09-08 17:55:51 |
Message-ID: | MN2PR15MB25600F0B55F23C486BC4DDFD85D49@MN2PR15MB2560.namprd15.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> From: Michael Lewis <mlewis(at)entrata(dot)com>
> Sent: Wednesday, September 8, 2021 13:40
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: ldh(at)laurent-hasson(dot)com; pgsql-performance(at)postgresql(dot)org
> Subject: Re: Better performance no-throw conversion?
>
> On Wed, Sep 8, 2021 at 11:33 AM Tom Lane <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "mailto:ldh(at)laurent-hasson(dot)com" <mailto:ldh(at)laurent-hasson(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | ldh@laurent-hasson.com | 2021-09-08 17:57:39 | RE: Better performance no-throw conversion? |
Previous Message | Michael Lewis | 2021-09-08 17:39:47 | Re: Better performance no-throw conversion? |