pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBetter performance no-throw conversion?
6+ messages / 4 participants
[nested] [flat]
* Better performance no-throw conversion?
@ 2021-09-08 17:17 ldh@laurent-hasson.com <ldh@laurent-hasson.com>
0 siblings, 2 replies; 6+ messages in thread
From: ldh@laurent-hasson.com @ 2021-09-08 17:17 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Better performance no-throw conversion?
@ 2021-09-08 17:31 Andrew Dunstan <andrew@dunslane.net>
parent: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
1 sibling, 1 reply; 6+ messages in thread
From: Andrew Dunstan @ 2021-09-08 17:31 UTC (permalink / raw)
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Better performance no-throw conversion?
@ 2021-09-08 17:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2021-09-08 17:32 UTC (permalink / raw)
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>; +Cc: pgsql-performance
"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.
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Better performance no-throw conversion?
@ 2021-09-08 17:39 Michael Lewis <mlewis@entrata.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Lewis @ 2021-09-08 17:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: ldh@laurent-hasson.com <ldh@laurent-hasson.com>; pgsql-performance
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Better performance no-throw conversion?
@ 2021-09-08 17:55 ldh@laurent-hasson.com <ldh@laurent-hasson.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 0 replies; 6+ messages in thread
From: ldh@laurent-hasson.com @ 2021-09-08 17:55 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> 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 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Better performance no-throw conversion?
@ 2021-09-08 17:57 ldh@laurent-hasson.com <ldh@laurent-hasson.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 6+ messages in thread
From: ldh@laurent-hasson.com @ 2021-09-08 17:57 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; pgsql-performance
> -----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 problematic for us.
Thank you,
Laurent.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-09-08 17:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-08 17:17 Better performance no-throw conversion? ldh@laurent-hasson.com <ldh@laurent-hasson.com>
2021-09-08 17:31 ` Andrew Dunstan <andrew@dunslane.net>
2021-09-08 17:57 ` ldh@laurent-hasson.com <ldh@laurent-hasson.com>
2021-09-08 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-08 17:39 ` Michael Lewis <mlewis@entrata.com>
2021-09-08 17:55 ` ldh@laurent-hasson.com <ldh@laurent-hasson.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox