Better performance no-throw conversion?

[l...@laurent-hasson.com]

Hello,

Some databases such as SQLServer (try_cast) or BigQuery (safe.cast) offer 
not-throw conversion. In general, these tend to perform better than custom UDFs 
that catch exceptions and are also simpler to use. For example, in Postgres, I 
have a function that does the following:

CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
RETURNS real AS $$
BEGIN
  RETURN case when str is null then val else str::real end;
EXCEPTION WHEN OTHERS THEN
  RETURN val;
END;
$$ LANGUAGE plpgsql COST 1 IMMUTABLE;

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?

Thank you!
Laurent Hasson.

Re: Better performance no-throw conversion?

[Andrew Dunstan]

On 9/8/21 1:17 PM, l...@laurent-hasson.com wrote:
>
> Hello,
>
>  
>
> Some databases such as SQLServer (try_cast) or BigQuery (safe.cast)
> offer not-throw conversion. In general, these tend to perform better
> than custom UDFs that catch exceptions and are also simpler to use.
> For example, in Postgres, I have a function that does the following:
>
>  
>
> CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
>
> RETURNS real AS $$
>
> BEGIN
>
>   RETURN case when str is null then val else str::real end;
>
> EXCEPTION WHEN OTHERS THEN
>
>   RETURN val;
>
> END;
>
> $$ LANGUAGE plpgsql COST 1 IMMUTABLE;
>
>  
>
> 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?
>
>  
>


Not that I know of, but you could probably do this fairly simply in C.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Re: Better performance no-throw conversion?

[Tom Lane]

"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.
The only way I can see to do it generically is to put a subtransaction
wrapper around the cast-function call, which is a lousy idea for a
couple of reasons:

1. It pretty much negates any performance benefit.

2. It'd be very hard to tell which errors are safe to ignore
and which are not (e.g., internal errors shouldn't be trapped
this way).

Of course, point 2 also applies to user-level implementations
(IOW, your code feels pretty unsafe to me).  So anything we might
do here would be an improvement.  But it's still problematic.

regards, tom lane

Re: Better performance no-throw conversion?

[Michael Lewis]

On Wed, Sep 8, 2021 at 11:33 AM Tom Lane  wrote:

> "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?

RE: Better performance no-throw conversion?

[l...@laurent-hasson.com]

> From: Michael Lewis  
> Sent: Wednesday, September 8, 2021 13:40
> To: Tom Lane 
> 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  wrote:
> "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.

RE: Better performance no-throw conversion?

[l...@laurent-hasson.com]

   >  -Original Message-
   >  From: Andrew Dunstan 
   >  Sent: Wednesday, September 8, 2021 13:31
   >  To: l...@laurent-hasson.com; pgsql-performa...@postgresql.org
   >  Subject: Re: Better performance no-throw conversion?
   >  
   >  
   >  On 9/8/21 1:17 PM, l...@laurent-hasson.com wrote:
   >  >
   >  > Hello,
   >  >
   >  >
   >  >
   >  > Some databases such as SQLServer (try_cast) or BigQuery (safe.cast)
   >  > offer not-throw conversion. In general, these tend to perform better
   >  > than custom UDFs that catch exceptions and are also simpler to use.
   >  > For example, in Postgres, I have a function that does the following:
   >  >
   >  >
   >  >
   >  > CREATE OR REPLACE FUNCTION toFloat(str varchar, val real)
   >  >
   >  > RETURNS real AS $$
   >  >
   >  > BEGIN
   >  >
   >  >   RETURN case when str is null then val else str::real end;
   >  >
   >  > EXCEPTION WHEN OTHERS THEN
   >  >
   >  >   RETURN val;
   >  >
   >  > END;
   >  >
   >  > $$ LANGUAGE plpgsql COST 1 IMMUTABLE;
   >  >
   >  >
   >  >
   >  > 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?
   >  >
   >  >
   >  >
   >  
   >  
   >  Not that I know of, but you could probably do this fairly simply in C.
   >  
   >  
   >  cheers
   >  
   >  
   >  andrew
   >  
   >  --
   >  Andrew Dunstan
   >  EDB: https://www.enterprisedb.com


Hello Andrew,

I work across multiple platforms (windows, linux, multiple managed cloud 
versions...) and a C-based solution would be problematic for us.

Thank you,
Laurent.