Thread: Better performance no-throw conversion?
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.
On 9/8/21 1:17 PM, ldh@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
"ldh@laurent-hasson.com" <ldh@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
planto 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
On Wed, Sep 8, 2021 at 11:33 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"ldh@laurent-hasson.com" <ldh@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?
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?
> From: Michael Lewis <mlewis@entrata.com> > Sent: Wednesday, September 8, 2021 13:40 > To: Tom Lane <tgl@sss.pgh.pa.us> > Cc: ldh@laurent-hasson.com; pgsql-performance@postgresql.org > Subject: Re: Better performance no-throw conversion? > > On Wed, Sep 8, 2021 at 11:33 AM Tom Lane <mailto:tgl@sss.pgh.pa.us> wrote: > "mailto:ldh@laurent-hasson.com" <mailto:ldh@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 planto 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 ensurethat values extracted from jsonb keys can be used as needed. Sanitizing the data on input is a long term goal, butnot 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 datasetof 1 million rows, what overhead does that exception incur? Is it only when there is an exception or is it on everyrow? > > 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 weeksback where it was identified that the exception block in the function I posted would cause a rough 3x-5x performanceoverhead for exception handling and was as expected. I identified a separate issue with the performance plummeting100x on certain Windows builds, but that's a separate issue. Thank you, Laurent.
> -----Original Message----- > From: Andrew Dunstan <andrew@dunslane.net> > Sent: Wednesday, September 8, 2021 13:31 > To: ldh@laurent-hasson.com; pgsql-performance@postgresql.org > Subject: Re: Better performance no-throw conversion? > > > On 9/8/21 1:17 PM, ldh@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 problematicfor us. Thank you, Laurent.