Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQWtX-0002nA-1n for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Aug 2022 16:36:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQWtV-0001he-TU for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Aug 2022 16:36:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQWtV-0001hV-CT for pgsql-hackers@lists.postgresql.org; Tue, 23 Aug 2022 16:36:49 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQWtS-0002FK-Qd for pgsql-hackers@lists.postgresql.org; Tue, 23 Aug 2022 16:36:49 +0000 Received: from [192.168.8.199] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id C8B1A21C33F2; Tue, 23 Aug 2022 19:36:44 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1661272605; bh=STpkY5lUoar3DvMUoksLw+HTSgW3jkcTtFtZy+rwWwM=; h=Date:Subject:To:Cc:References:From:In-Reply-To; b=WFvJmXmi5yA9H7fotDzKTEWsau+/A3tnTJegpDxnEcTtNPx2+SIILvxhXUp81NUKT cHOokveLQgh+zfYRmoUfrJjH6gyM9bP/kI0TsYbaY9Fr8MuWlQtJdz29hPdbs9GPR5 Tm5upHnXeCoXWaTT9aZqFWS+2V/XNd44vkZa15Y0= Content-Type: multipart/alternative; boundary="------------Qkl6PZ41drcfbbAisdjOBf00" Message-ID: <447584bb-d14e-383d-2d26-f8ed4000d45f@postgrespro.ru> Date: Tue, 23 Aug 2022 19:36:11 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: SQL/JSON features for v15 Content-Language: en-US To: Pavel Stehule , Andres Freund Cc: Robert Haas , "Jonathan S. Katz" , PostgreSQL Hackers , Andrew Dunstan , Tom Lane , Michael Paquier , John Naylor , Amit Langote References: <20220815223853.itrlpqgpqheg6r6a@awork3.anarazel.de> <225d341b-f310-eed0-ceff-b8ec61669d9f@postgrespro.ru> <20220816021425.5sa6gx67zmpou3bc@awork3.anarazel.de> <8d0ffc54-469a-ffab-68a3-8b547e24a08d@postgresql.org> <7d83684b-7932-9f29-400b-0beedfafcdd4@postgrespro.ru> <69022273-e7e3-6d1a-10f8-d1b4740e4b97@postgresql.org> <92cab50c-152b-84fc-fd49-12b936113a3d@postgresql.org> <20220823155511.ffyntlyjl6wrgljv@awork3.anarazel.de> From: Nikita Glukhov In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Qkl6PZ41drcfbbAisdjOBf00 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 23.08.2022 19:06, Pavel Stehule wrote: > Hi > > út 23. 8. 2022 v 17:55 odesílatel Andres Freund > napsal: > > Hi, > > On 2022-08-23 10:51:04 -0400, Robert Haas wrote: > > I do not think that using subtransactions as part of the expression > > evaluation process is a sound idea pretty much under any > > circumstances. Maybe if the subtransations aren't commonly > created and > > don't usually get XIDs there wouldn't be a big problem in practice, > > but it's an awfully heavyweight operation to be done inside > expression > > evaluation even in corner cases. I think that if we need to make > > certain operations that would throw errors not throw errors, we need > > to refactor interfaces until it's possible to return an error > > indicator up to the appropriate level, not just let the error be > > thrown and catch it. > > I don't think that's quite realistic - that's the input/output > functions for > all types, basically.  I'd be somewhat content if we'd a small > list of very > common coercion paths we knew wouldn't error out, leaving things > like OOM > aside. Even just knowing that for ->text conversions would be a > huge deal in > the context of this patch.  One problem here is that the whole > type coercion > infrastructure doesn't make it easy to know what "happened inside" > atm, one > has to reconstruct it from the emitted expressions, where there can be > multiple layers of things to poke through. > > > The errors that should be handled are related to json structure > errors. I don't think so we have to handle all errors and all conversions. > > The JSON knows only three types - and these conversions can be written > specially for this case - or we can write json io routines to be able > to signal error > without an exception. I also wanted to suggest to limit the set of returning types to the predefined set of JSON-compatible types for which can write safe conversion functions: character types (text, char), boolean, number types (integers, floats types, numeric), datetime types. The SQL standard even does not require support of other returning types. For the float8 and datetime types we already have safe input functions like float8in_internal_opt_error() and parse_datetime() which are used inside jsonpath and return error code instead of throwing errors. We need to implement numeric_intN_safe() and maybe a few other trivial functions like that. The set of returning types, for which we do not need any special coercions, is very limited: json, jsonb, text. More precisely, even RETURNING json[b] can throw errors in JSON_QUERY(OMIT QUOTES), and we also need safe json parsing, but it can be easily done with pg_parse_json(), which returns error code. --------------Qkl6PZ41drcfbbAisdjOBf00 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 23.08.2022 19:06, Pavel Stehule wrote:
Hi

út 23. 8. 2022 v 17:55 odesílatel Andres Freund <andres@anarazel.de> napsal:
Hi,

On 2022-08-23 10:51:04 -0400, Robert Haas wrote:
> I do not think that using subtransactions as part of the expression
> evaluation process is a sound idea pretty much under any
> circumstances. Maybe if the subtransations aren't commonly created and
> don't usually get XIDs there wouldn't be a big problem in practice,
> but it's an awfully heavyweight operation to be done inside expression
> evaluation even in corner cases. I think that if we need to make
> certain operations that would throw errors not throw errors, we need
> to refactor interfaces until it's possible to return an error
> indicator up to the appropriate level, not just let the error be
> thrown and catch it.

I don't think that's quite realistic - that's the input/output functions for
all types, basically.  I'd be somewhat content if we'd a small list of very
common coercion paths we knew wouldn't error out, leaving things like OOM
aside. Even just knowing that for ->text conversions would be a huge deal in
the context of this patch.  One problem here is that the whole type coercion
infrastructure doesn't make it easy to know what "happened inside" atm, one
has to reconstruct it from the emitted expressions, where there can be
multiple layers of things to poke through.

The errors that should be handled are related to json structure errors. I don't think so we have to handle all errors and all conversions.

The JSON knows only three types - and these conversions can be written specially for this case - or we can write json io routines to be able to signal error
without an exception.


I also wanted to suggest to limit the set of returning types to the
predefined set of JSON-compatible types for which can write safe
conversion functions: character types (text, char), boolean, number
types (integers, floats types, numeric), datetime types.  The SQL
standard even does not require support of other returning types.

For the float8 and datetime types we already have safe input functions
like float8in_internal_opt_error() and parse_datetime() which are used
inside jsonpath and return error code instead of throwing errors.
We need to implement numeric_intN_safe() and maybe a few other trivial
functions like that.

The set of returning types, for which we do not need any special
coercions, is very limited: json, jsonb, text.  More precisely,
even RETURNING json[b] can throw errors in JSON_QUERY(OMIT QUOTES), 
and we also need safe json parsing, but it can be easily done 
with pg_parse_json(), which returns error code.
--------------Qkl6PZ41drcfbbAisdjOBf00--