Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2CId-00FS2t-UE for pgsql-bugs@arkaria.postgresql.org; Sun, 12 Nov 2023 15:22:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r2CIa-00AmKg-SZ for pgsql-bugs@arkaria.postgresql.org; Sun, 12 Nov 2023 15:22:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2CIa-00AmKX-9x for pgsql-bugs@lists.postgresql.org; Sun, 12 Nov 2023 15:22:56 +0000 Received: from janus.karlpinc.com ([173.161.46.12] helo=smtp.karlpinc.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2CIU-005DzZ-Od for pgsql-bugs@lists.postgresql.org; Sun, 12 Nov 2023 15:22:54 +0000 Received: from slate.karlpinc.com (unknown [192.168.1.14]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by smtp.karlpinc.com (Postfix) with ESMTPS id E9E77A6A8; Sun, 12 Nov 2023 09:22:47 -0600 (CST) Received: from slate.karlpinc.com (localhost [IPv6:::1]) by slate.karlpinc.com (Postfix) with ESMTPS id 29C9C3FB8B; Sun, 12 Nov 2023 09:22:47 -0600 (CST) Date: Sun, 12 Nov 2023 09:22:44 -0600 From: "Karl O. Pinc" To: "David G. Johnston" Cc: "pgsql-bugs@lists.postgresql.org" Subject: Re: BUG #18190: PL/pgSQL does not document comparison operations on RECORD (or ROW) types Message-ID: <20231112092244.5cad27f8@slate.karlpinc.com> In-Reply-To: References: <18190-ba5eff0af347078d@postgresql.org> X-Mailer: Claws Mail 3.17.8 (GTK+ 2.24.33; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi David, Thanks for your thoughtful reply. tldr; My complaint is spurious. Maybe the docs could be further improved in some way anyway. On Sat, 11 Nov 2023 18:13:35 -0700 "David G. Johnston" wrote: > On Friday, November 10, 2023, PG Bug reporting form > wrote: > > Nothing is said about how to compare entire RECORD type variables, > > or ROW type variables. =20 >=20 >=20 > There is, in the chapters with the word compare in their titles. >=20 > https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIO= NS-COMPARISON >=20 > https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTI= ONS-COMPARISONS It turns out I was not having a problem comparing composite variables with composite variables but composite variables with NULL. As you point out below. > You couldn=E2=80=99t make something less difficult to read? Like a > self-contained select that shows true when you expected false? Or > even a DO block with no dynamic SQL? Apologies. I was excited about showing a (somewhat) practical example of where I got surprised. I didn't stop to think about simpler ways to assign a RECORD variable different values to come up with the comparison operation which was then true focus of the issue. I'd been looking at the code for some time and stripping it to the simplest test of an EXECUTE result I could come up with since that was where I was having a problem. When I finally figured out the problem was with the comparison the code seemed simple and I didn't think to strip out the EXECUTE. > IF problem IS NOT NULL THEN >=20 >=20 > As noted in the docs, =E2=80=9Cnot (composite is null)=E2=80=9D is not eq= uivalent to > =E2=80=9Ccomposite is not null=E2=80=9D; you usually want the former. Are you referring to here https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS= -COMPARISON where it says: "If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS N= OT NULL is true when the row expression itself is non-null and all the row'= s fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do= not always return inverse results for row-valued expressions; in particula= r, a row-valued expression that contains both null and non-null fields will= return false for both tests. In some cases, it may be preferable to write = row IS DISTINCT FROM NULL or row IS NOT DISTINCT FROM NULL, which will simp= ly check whether the overall row value is null without any additional tests= on the row fields." The above is what I should have been reading. I believe what I would want (if my particular application wasn't better suited to GET DIAGNOSITCS) is the IS DISTINCT FROM NULL construct. I do see a very minor problem here, in that the text writes about "row expressions" not composite data types. The problem being that it's not entirely clear (to me) that a RECORD type is a row expression, but it is clearly a composite. Or so it seems to me. > The behavior involved is in no way specific to pl/pgsql; it doesn=E2=80= =99t > seem warranted to choose to link to SQL documentation from pl/pgsql > generally. The writer of such code can reference the SQL docs for any > SQL they need to write. FWIW, I spent a long time looking for the relevant part of the docs and for some reason didn't find it. This is unusual. I usually find the organization of the docs very easy to navigate. I don't have a good explanation of what went wrong but spent enough time at it that filing a bug report seemed worthwhile. Perhaps I'm too used to reading the PL/pgSQL chapter, which makes it sound like RECORD variables are strictly a PL/pgSQL construct. (And maybe they are.) It's unclear why this would matter, and how such a thought would throw off my searching. But something did. =20 (FWIW, The sentence "Note that RECORD is not a true data type, only a placeholder." is pretty mysterious. I don't see anything that informs me about what a RECORD variable is, composite, ROW type, or whatever.) You can close this bug, or whatever else makes sense to you. Thank you for your attention. I hope the above is in some way useful and the whole process is not a complete waste of your time. Regards, Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein