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 <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1qddwM-004FZr-LB
	for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Sep 2023 21:50:30 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1qddwL-004t7O-JW
	for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Sep 2023 21:50:29 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <vik@postgresfriends.org>)
	id 1qddwA-004qkC-8J
	for pgsql-hackers@lists.postgresql.org; Tue, 05 Sep 2023 21:50:18 +0000
Received: from relay4-d.mail.gandi.net ([2001:4b98:dc4:8::224])
	by magus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <vik@postgresfriends.org>)
	id 1qddw2-003H62-7Y
	for pgsql-hackers@lists.postgresql.org; Tue, 05 Sep 2023 21:50:17 +0000
Received: by mail.gandi.net (Postfix) with ESMTPSA id 54430E0003;
	Tue,  5 Sep 2023 21:50:05 +0000 (UTC)
Message-ID: <d5a08654-33ea-0a12-8f95-e0029f65c75e@postgresfriends.org>
Date: Tue, 5 Sep 2023 23:50:04 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.15.0
Subject: Re: information_schema and not-null constraints
To: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Pg Hackers <pgsql-hackers@lists.postgresql.org>, Tom Lane
 <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter@eisentraut.org>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
References: <202309051715.7ghhtqh76hyw@alvherre.pgsql>
From: Vik Fearing <vik@postgresfriends.org>
Content-Language: en-US
In-Reply-To: <202309051715.7ghhtqh76hyw@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-GND-Sasl: vik@postgresfriends.org
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/d5a08654-33ea-0a12-8f95-e0029f65c75e%40postgresfriends.org>
Precedence: bulk

On 9/5/23 19:15, Alvaro Herrera wrote:
> On 2023-Sep-05, Alvaro Herrera wrote:
> 
> Looking now at what to do for CHECK_CONSTRAINTS with domain constraints,
> I admit I'm completely confused about what this view is supposed to
> show.  Currently, we show the constraint name and a definition like
> "CHECK (column IS NOT NULL)".  But since the table name is not given, it
> is not possible to know to what table the column name refers to.  For
> domains, we could show "CHECK (VALUE IS NOT NULL)" but again with no
> indication of what domain it applies to, or anything at all that would
> make this useful in any way whatsoever.

Constraint names are supposed to be unique per schema[1] so the view 
contains the minimum required information to identify the constraint.

> So this whole thing seems pretty futile and I'm disinclined to waste
> much time on it.

Until PostgreSQL either
   A) obeys the spec on this uniqueness, or
   B) decides to deviate from the information_schema spec;
this view will be completely useless for actually getting any useful 
information.

I would like to see us do A because it is the right thing to do.  Our 
autogenerated names obey this rule, but who knows how many duplicate 
names per schema are out there in the wild from people specifying their 
own names.

I don't know what the project would think about doing B.


[1] SQL:2023-2 11.4 <table constraint definition> Syntax Rule 4
-- 
Vik Fearing