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 1qdeJo-004Gq1-BP for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Sep 2023 22:14:44 +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 1qdeJl-004wzC-Io for pgsql-hackers@arkaria.postgresql.org; Tue, 05 Sep 2023 22:14:41 +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 ) id 1qdeJl-004wyc-6X for pgsql-hackers@lists.postgresql.org; Tue, 05 Sep 2023 22:14:41 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qdeJf-003HJT-79 for pgsql-hackers@lists.postgresql.org; Tue, 05 Sep 2023 22:14:39 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-1d4e0c2901bso1773674fac.0 for ; Tue, 05 Sep 2023 15:14:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1693952073; x=1694556873; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=IcUeFCuhZ8p/laiAAvLrvFsEOYJCv8Z1Y69xe4GBejM=; b=IegAiELbM8yOH0PjIWzhe4dN5MGwjigbSgT3iyKu1shWfDTIhEw+e1eGcs2ZQOCq9v 46ePbcVCIsUqJMrBlya4Q9qKRa2hD816hCuW++Hu7en06kSf2ZYPfqoM38BtCZ0ZPifI I/G4K5xOYW2MRAssgeYRNrTOQAL0U4wMcVUAW2Ck7YkN+Nl2K60aQkSVEmOPu6f2Pgdu yOLWPc1jePTefs7rwks3S+3dnNMe9FV2ZeQxCUbIZfBbRLpwE7yyxqs/NAEil7Urvlix c4qu1S9Xqz3wszi1bpt6XItBAEbAFXWJpLB6ZIbqbHigNRNYGGKjFLLQXEc1NhySHFNr lMCw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1693952073; x=1694556873; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=IcUeFCuhZ8p/laiAAvLrvFsEOYJCv8Z1Y69xe4GBejM=; b=MRTTZ6m0oVMhXxlvwtuL481Sl2MPFHjCOkGnTmz6C1x+ul8DNm+2BqQMXKfabrnHgq 9mUNlU2pnGc8BnH5YXQNcD4M9uUCtpaZk7NrOPh5Ushk1DqJ+riQ3GP27X+GrCuEvAz1 YXM1TmRixR8lEWyWdgU8RjAWUjdqpUEPTjd+xAYx6iz/J3whwEeSlo8JXut/Fk0Ym2dM zr9j3FaV6CkU0gJslsUOnJfzl1rs0hCRndGon3fspX+IIiW/BEx7BDujBmPMjL/itvsS ycJV+1Uexfa5Qi/v62eI2lHzZMMfA9NAnKW/JJtzwIxdFa7qAmKh/6wQueqyNW/9A8DW VgBQ== X-Gm-Message-State: AOJu0YzohMCjs3GZjCY9vi2lySVP9VD3hEXdDkeLjC0OKoXsSqogQDXB PCqLOazFC0ITXA74dDEcs7LcIhghGO8iDBFb0dw= X-Google-Smtp-Source: AGHT+IGk5yq2BouyZ1akQKIHGH9iZR1RwWoB0G4MZY0vMjyjAmXMkL1+qd4AUaq0XgE9XmxUb0WDvg710rZXV2W4rS0= X-Received: by 2002:a05:6870:b14b:b0:1cd:24a1:1c56 with SMTP id a11-20020a056870b14b00b001cd24a11c56mr17578789oal.19.1693952072863; Tue, 05 Sep 2023 15:14:32 -0700 (PDT) MIME-Version: 1.0 References: <202309051715.7ghhtqh76hyw@alvherre.pgsql> In-Reply-To: From: "David G. Johnston" Date: Tue, 5 Sep 2023 15:14:15 -0700 Message-ID: Subject: Re: information_schema and not-null constraints To: Vik Fearing Cc: Alvaro Herrera , Pg Hackers , Tom Lane , Peter Eisentraut , Peter Eisentraut Content-Type: multipart/alternative; boundary="00000000000091d2620604a3f39e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000091d2620604a3f39e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Sep 5, 2023 at 2:50=E2=80=AFPM Vik Fearing wrote: > 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, i= t > > 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. > I'm presuming that the view constraint_column_usage [1] is an integral part of all this though I haven't taken the time to figure out exactly how we are implementing it today. I'm not all that for either A or B since the status quo seems workable. Though ideally if the system has unique names per schema then everything should just work - having the views produce duplicated information (as opposed to nothing) if they are used when the DBA doesn't enforce the standard's requirements seems plausible. David J. [1] https://www.postgresql.org/docs/current/infoschema-constraint-column-usage.= html --00000000000091d2620604a3f39e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Sep 5, 2023 at 2:50=E2=80=AFPM Vik Fearing <vik@postgresfriends.org> wro= te:
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 constraint= s,
> I admit I'm completely confused about what this view is supposed t= o
> show.=C2=A0 Currently, we show the constraint name and a definition li= ke
> "CHECK (column IS NOT NULL)".=C2=A0 But since the table name= is not given, it
> is not possible to know to what table the column name refers to.=C2=A0= 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.

I'm presuming that the view constrain= t_column_usage [1] is an integral part of all this though I haven't tak= en the time to figure out exactly how we are implementing it today.

I'm not all that for either A or B since the status quo s= eems workable.=C2=A0 Though ideally if the system has unique names per sche= ma then everything should just work - having the views produce duplicated i= nformation (as opposed to nothing) if they are used when the DBA doesn'= t enforce the standard's requirements seems plausible.

David J.

[1]=C2=A0https://www.p= ostgresql.org/docs/current/infoschema-constraint-column-usage.html

--00000000000091d2620604a3f39e--