From: Tom Lane <tgl@sss.pgh.pa.us>
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Better performance no-throw conversion?
In-reply-to: 
 <MN2PR15MB2560C23261EFFBFC9F437A5B85D49@MN2PR15MB2560.namprd15.prod.outlook.com>
References: 
 <MN2PR15MB2560C23261EFFBFC9F437A5B85D49@MN2PR15MB2560.namprd15.prod.outlook.com>
Comments: In-reply-to "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
	message dated "Wed, 08 Sep 2021 17:17:35 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3435888.1631122378.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 08 Sep 2021 13:32:58 -0400
Message-ID: <3435889.1631122378@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3435889.1631122378%40sss.pgh.pa.us>
Precedence: bulk

"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> Some databases such as SQLServer (try_cast) or BigQuery (safe.cast) offe=
r not-throw conversion.
> ...
> I couldn't find a reference to such capabilities in Postgres and wondere=
d 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