PL/pgSQL — "commit" illegal in the executable section of a block statement that has an exception section

I work for YugaByte, Inc (www.yugabyte.com ). YugabyteDB re-uses the source code that implements the “upper half” of PostgreSQL Version 11.2. See here: https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/ This means that the

Cannot commit or rollback in “security definer” PL/pgSQL proc

Here’s a cut-down version of Umair Shahid’s blog post here: https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-1/ __ create table t(k int primary key, v int not null); create

Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

Bryn Llewellyn wrote: > ...I wrote my own wrapper for jsonb_build_array() > and jsonb_build_object(): > > create function my_jsonb_build( > kind in varchar, > variadic_elements in varchar) > returns jsonb > immutable > language plpgsql > as $bo

Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

Andrew Dunstan wrote: Bryn Llewellyn wrote: > > Andrew replied > > The function above has many deficiencies, including lack of error > checking and use of 'execute' which will significantly affect > performance. Still, if it works for you, that's your affair. > > Thes

Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

On 16-Feb-2020, at 16:40, Andrew Dunstan wrote: On 2/16/20 7:25 PM, Bryn Llewellyn wrote: > > B.t.w., you earlier said “The double quotes [around “dog”] serve a specific > purpose, to allow values containing commas to be treated as a single value > (see syntax details for the

jsonb_object() seems to be buggy. jsonb_build_object() is good.

sion 2.0.11.0. See this blogpost: “Distributed PostgreSQL on a Google Spanner Architecture—Query Layer” https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/ YugabyteDB uses the PostgreSQL source code for its SQL upper half. Regards, Bryn Llewellyn, Yugabyte

Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

. Johnston wrote: On Friday, February 14, 2020, Bryn Llewellyn mailto:b...@yugabyte.com>> wrote: select jsonb_pretty(jsonb_object( '{a, 17, b, "dog", c, true}'::varchar[] )) In other words, do the double quotes around "dog" have no effect? That would be a bad thing—and it

Re: jsonb_object() seems to be buggy. jsonb_build_object() is good.

of the jsonb_object() function. The doc (“Builds a JSON object out of a text array.”) is simply too terse to inform an answer to this question. On 14-Feb-2020, at 18:28, Vik Fearing wrote: On 15/02/2020 03:21, Bryn Llewellyn wrote: > Now execute this supposed functional equivalent: > > sel

Syntax rules for a text value inside the literal for a user-defined type—doc section “8.16.2. Constructing Composite Values”

I’m using PostgreSQL Version 11.2. Try this: create type rt as (a text, b text); create table t(k serial primary key, r rt); insert into t(r) values ('("a","b")'), ('( "e" , "f" )'), ('( "g (h)" , "i, j" )'); select k, '>'||(r).a||'<' as a, '>'||(r).b||'<' as b from t order by k;

Re: Syntax rules for a text value inside the literal for a user-defined type—doc section “8.16.2. Constructing Composite Values”

On 02-Apr-2020, at 19:25, Tom Lane wrote: Bryn Llewellyn writes: > The documentation in section “8.16.2. Constructing Composite Values” here: > https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-IO-SYNTAX > <https://www.postgresql.org/docs/11/rowtypes.html#ROWTYP

Re: Syntax rules for a text value inside the literal for a user-defined type—doc section “8.16.2. Constructing Composite Values”

On 03-Apr-2020, at 00:05, David G. Johnston wrote: On Thu, Apr 2, 2020 at 8:46 PM Bryn Llewellyn mailto:b...@yugabyte.com>> wrote: On 02-Apr-2020, at 19:25, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Bryn Llewellyn mailto:b...@yugabyte.com>> writes: > The documentati

Re: Have I found an interval arithmetic bug?

> On 05-Apr-2021, at 13:35, Bruce Momjian wrote: > > On Mon, Apr 5, 2021 at 01:06:36PM -0700, Bryn Llewellyn wrote: >>> On 05-Apr-2021, at 11:37, Bruce Momjian wrote On: >>> Mon, Apr 5, 2021 at 01:15:22PM -0500, Justin Pryzby wrote : >> >>

Re: Have I found an interval arithmetic bug?

br...@momjian.us wrote: > I have just posted a new version of the patch which I think covers all the > right areas. I found the relevant email from you to pgsql-hackers here: https://www.postgresql.org/message-id/20210402234732.GA29125%40momjian.us You said: > I have modified the patch to

Re: Have I found an interval arithmetic bug?

> On 08-Apr-2021, at 10:24, Bruce Momjian wrote: > > On Mon, Apr 5, 2021 at 02:01:58PM -0400, Bruce Momjian wrote: >> On Mon, Apr 5, 2021 at 11:33:10AM -0500, Justin Pryzby wrote: >> Well, bug or not, we are not going to change back branches for this, and >> if you want a larger discussion, it

Re: Have I found an interval arithmetic bug?

br...@momjian.us wrote: > > z...@yugabyte.com wrote: >> Among previous examples given by Bryn, the following produces correct result >> based on Bruce's patch. >> >> # select interval '-1.7 years 29.4 months'; >> interval >> >> 9 mons 12 days > > Yes, that changed is

Re: Have I found an interval arithmetic bug?

> On 12-Apr-2021, at 17:25, Bruce Momjian wrote: > > On Mon, Apr 12, 2021 at 05:20:43PM -0700, Bryn Llewellyn wrote: >> I’d argue that the fact that this: >> >> ('0.3 months'::interval) + ('0.7 months'::interval) >> >> Is reported as '30 days' and not '1

Re: Have I found an interval arithmetic bug?

> On 12-Apr-2021, at 17:00, Bruce Momjian wrote: > > On Mon, Apr 12, 2021 at 07:38:21PM -0400, Tom Lane wrote: >> Bruce Momjian writes: >>> On Mon, Apr 12, 2021 at 03:09:48PM -0700, Bryn Llewellyn wrote: >>>> After all, you've bitten the bullet now and ch

Re: Have I found an interval arithmetic bug?

> t...@sss.pgh.pa.us wrote: > > br...@momjian.us writes: >> b...@yugabyte.com wrote: >>> After all, you've bitten the bullet now and changed the behavior. This >>> means that the semantics of some extant applications will change. So... in >>> for a penny, in for a pound? > >> The docs now say:

Re: Have I found an interval arithmetic bug?

> On 21-Jul-2021, at 02:58, Tom Lane wrote: > > Dean Rasheed writes: >> Hmm, looking at this whole thread, I have to say that I prefer the old >> behaviour of spilling down to lower units. > >> For example, with this patch: > >> SELECT '0.5 weeks'::interval; >> interval >> -- >> 4

Re: Have I found an interval arithmetic bug?

> On 21-Jul-2021, at 01:23, Dean Rasheed wrote: > > On Wed, 21 Jul 2021 at 03:48, Bruce Momjian wrote: >> >> this example now gives me concern: >> >>SELECT INTERVAL '1.06 months 1 hour'; >> interval >>--- >> 1 mon 2 days 01:00:00 >>

Re: Have I found an interval arithmetic bug?

> On 21-Jul-2021, at 17:07, Bruce Momjian wrote: > > On Wed, Jul 21, 2021 at 01:29:49PM -0400, Tom Lane wrote: >> Bryn Llewellyn writes: >>> It was me that started the enormous thread with the title “Have I found an >>> interval arithmetic bug?” on 01-Ap

Re: Have I found an interval arithmetic bug?

> On 23-Jul-2021, at 08:05, Bruce Momjian wrote: > > On Thu, Jul 22, 2021 at 03:17:52PM -0700, Zhihong Yu wrote: >> On Thu, Jul 22, 2021 at 2:59 PM Zhihong Yu wrote: >>Hi, >> >>- tm->tm_mon += (fval * MONTHS_PER_YEAR); >>+ tm->tm_mon += rint(fval

Re: Have I found an interval arithmetic bug?

> On 27-Jul-2021, at 14:13, Bruce Momjian wrote: > > On Tue, Jul 27, 2021 at 04:01:54PM -0400, Tom Lane wrote: >> Bruce Momjian writes: >>> I went ahead and modified the interval multiplication/division functions >>> to use the same logic as fractional interval units: >> >> Wait. A. Minute. >>

Re: Have I found an interval arithmetic bug?

> On 23-Jul-2021, br...@momjian.us wrote: > > On Fri, Jul 23, 2021 at 10:55:11AM -0700, Bryn Llewellyn wrote: >> SELECT >> '1.2345 months 1.2345 days 1.2345 seconds'::interval = >> '1 month 1 day 1 second'::interval*1.2345; >> >> In 13.3, the re