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 1nbxNz-0007cE-8d for pgsql-sql@arkaria.postgresql.org; Wed, 06 Apr 2022 04:35:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nbxNy-0004hf-6X for pgsql-sql@arkaria.postgresql.org; Wed, 06 Apr 2022 04:35:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbxMu-0000MT-NP for pgsql-sql@lists.postgresql.org; Wed, 06 Apr 2022 04:34:08 +0000 Received: from mail-ed1-x52a.google.com ([2a00:1450:4864:20::52a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nbxMs-0007g7-5S for pgsql-sql@lists.postgresql.org; Wed, 06 Apr 2022 04:34:07 +0000 Received: by mail-ed1-x52a.google.com with SMTP id g22so1183771edz.2 for ; Tue, 05 Apr 2022 21:34:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=GCNAphaJHHxpYsi6AI5KCNcycLThB+SNHOGs9D9CkN8=; b=i1IMhtQGMxjjI/ThXIr14g4FSPiJ8pydPXHAbkVRI7owMkYOckYkX3Wbm3c9N0UzV6 ZKsMrMRONwkcOCMI/4wEXEzHCCPf4fy0vUnrNZjaC4W3A1YxAQHF8KC/yRPtMq3Srq0k 4nPRAJLNOHgU0OYycyRDLqWHUww8G6XlcNXRaALyenwAuDfamymY6QZOtDHYw43ErsOf eNrgoV6bAAJs7VCogRpg+NRbRou2i1RIjX1lYOVDTSpsBzcj2+WDaq+l6pnzCmg6sXb9 4DTgsKWj+4jN0XnlCSqtTQdd18Ns6rO9bcyZnOYXWy5QZy2C96JBpu/xrpGVpliTZ9wo sbdg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=GCNAphaJHHxpYsi6AI5KCNcycLThB+SNHOGs9D9CkN8=; b=SklR3Z3D8Jc0IPu4EBxd6dTa5owkkb/Xk9QaG4w8xeEqiil+WveP8ewFFbvVrAAk5l mZp+hFrzm2WbvnGUmXuqqr0/fxgTLK/6JGWXdC0TLiNPz4TWrmJJZ0LtxfmPcjt/zjXI Rvk4emwWK8BmU8PqmyGWFYdahZNLtj5GkyP2mkfxwLLAZ7F/Rixdsys1XX6wH+KzcHgz Ws8Tn1TvDCivnaZJY3UgOMw1aG/ZvVrgsPNAQ6KBhj32uAuq8xX92SGeiFZ4R8T+AIx8 G+sYApTRLABHQXoeIwaYNgFdsi7s23Yew0Ohjq6H9iLXCZDrUakqWlxgVsWKTjCp0+ZJ iy3g== X-Gm-Message-State: AOAM531KUgdNaHn1YoW+90x0dvb/b4d1pblsWVnLodfezNyVhDbIVn9w 3/O0mvCjkn0QDWxZbbacjsrbXtgNY+U+R3KKMezjqhc6 X-Google-Smtp-Source: ABdhPJwuEY/YzT6jNrKdpce/HcuwFk4nHPytAfjCPuSbhOF6dNY1asUTXj3IiSKkqMtvFH/iU0aBdRdwE7PoWoIvr7w= X-Received: by 2002:a05:6402:2741:b0:41c:dffb:51cd with SMTP id z1-20020a056402274100b0041cdffb51cdmr7031502edd.328.1649219644512; Tue, 05 Apr 2022 21:34:04 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 5 Apr 2022 21:33:47 -0700 Message-ID: Subject: Re: Does postgresql know the check condition is valid or not. or can check deduce from multiple conditions To: Jian He Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000115e1105dbf4df23" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000115e1105dbf4df23 Content-Type: text/plain; charset="UTF-8" On Tue, Apr 5, 2022 at 9:08 PM Jian He wrote: > If the question is stupid, please tell me why. > Not stupid, but the question as phrased is making an assumption about how the system works that isn't true. Or, at least, I don't understand what you mean by "deduce it" and "query it"? The fact those check constraints exist shows you PostgreSQL doesn't deduce it in order to prevent their creation. There is also nothing provided to query existing constraints and sanity check them. Nor is such a feature all that valuable - such constraints are seldom written and the expected cursory testing that should go along with such a thing, or even the fact that the system should probably not function while they exist, means that when they do get created they are quickly discovered. For the redundant check constraint, it is not so easily discovered but neither does it seem like a big deal - so long as the relevant operator is cheap to execute. That tends to be the case for check constraints. When they are not, they usually are not easily figured out to be logically redundant either. I wrote the following having mis-understood your question. I will leave it because it may be informative, and is at least tangentially on-topic: IIUC, queries look at statistics to make decisions. They will also look at, I think, unique constraints at the table level and not null constraints at the column level. Not sure about references - I think the unique and stats handles those sufficiently. CREATE TABLE emp (test_check int check ( test_check >1 and test_check > < 0 )); > > alter table emp VALIDATE CONSTRAINT emp_test_check_check;``` > > select * from pg_constraint where conname = 'emp_test_check_check'; > > Even with the above mentioned step, does postgresql know above check > constraint condition always false. > Indirectly, it will know, using statistics, that either the table itself is empty or that the most common, and only, value for the column is null (null_frac = 1.0) > > another similar question: > can postgresql deduce from > CREATE TABLE emp1 (test_check int check ( test_check >1 and test_check > > 10 )); > > to > > CREATE TABLE emp1 (test_check int check ( test_check > 10 )); > I think only in the resulting statistics, most likely in this case the lowest histogram_bounds boundary should be 11. David J. --000000000000115e1105dbf4df23 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 5, 2022 at 9:08 PM Jian He <hejian.mark@gmail.com> wrote:

If = the question=C2=A0is stupid, please tell me why.=C2=A0

Not stupid, but the question as phrased is making an= assumption about how the system works that isn't true.=C2=A0 Or, at le= ast, I don't understand what you mean by "deduce it" and &quo= t;query it"?=C2=A0 The fact those check constraints exist=C2=A0shows y= ou PostgreSQL doesn't deduce it in order to prevent their creation.=C2= =A0 There is also nothing provided to query existing constraints and sanity= check them.=C2=A0 Nor is such a feature all that valuable - such constrain= ts are seldom written and the expected cursory testing that should go along= with such a thing, or even the fact that the system should probably not fu= nction while they exist, means that when they do get created they are quick= ly discovered.=C2=A0 For the redundant check constraint, it is not so easil= y discovered but neither does it seem like a big deal - so long as the rele= vant operator is cheap to execute.=C2=A0 That tends to be the case for chec= k constraints.=C2=A0 When they are not, they usually are not easily figured= out to be logically redundant either.

I wrote the fol= lowing having mis-understood your question.=C2=A0 I will leave it because i= t may be informative, and is at least tangentially on-topic:

IIUC, queries look at statistics to make decisions.=C2=A0 They will = also look at, I think, unique constraints at the table level and not null c= onstraints at the column level.=C2=A0 Not sure about references - I think t= he unique and stats handles those sufficiently.

=

CREATE=C2=A0TABLE=C2=A0emp (test_check int=C2=A0c= heck=C2=A0( test_check >1=C2=A0and=C2=A0test_check <=C2=A00=C2=A0));<= /p>

alter= table emp =C2=A0VALIDATE CONSTRAINT emp_test_check_check;```

select * from pg= _constraint where conname =3D 'emp_test_check_check';

Even w= ith the above mentioned step, does postgresql know above check constraint c= ondition always false.

Indi= rectly, it will know, using statistics, that either the table itself is emp= ty or that the most common, and only, value for the column is null (null_fr= ac =3D 1.0)


another simi= lar question:
can postgresql deduce from
CREATE TABLE emp1 (test_chec= k int check ( test_check >1 and test_check > 10 ));

to

CREATE TABLE emp1 (test_= check int check ( test_check > 10 ));

I think only in the resulting statistics, most likely in this case= the lowest histogram_bounds boundary should be 11.

Da= vid J.

--000000000000115e1105dbf4df23--