From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com>, amul sul <sul_amul(at)yahoo(dot)co(dot)in> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Passing initially_valid values instead of !skip_validation to StoreRelCheck() in AddRelationNewConstraints() |
Date: | 2015-12-09 02:19:51 |
Message-ID: | 56678FC7.9010306@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 2015/12/09 5:50, Robert Haas wrote:
> On Thu, Dec 3, 2015 at 3:52 AM, amul sul <sul_amul(at)yahoo(dot)co(dot)in> wrote:
>> Can we pass initially_valid instead of !skip_validation to StoreRelCheck() in AddRelationNewConstraints(), as shown below?
>
> The comments say this:
>
> * If skip_validation is true then we skip checking that the existing rows
> * in the table satisfy the constraint, and just install the catalog entries
> * for the constraint. A new FK constraint is marked as valid iff
> * initially_valid is true. (Usually skip_validation and initially_valid
> * are inverses, but we can set both true if the table is known empty.)
>
> These comments are accurate. If you create a FK constraint as not
> valid, the system decides that it's really valid after all, but if you
> add a CHECK constraint as not valid, the system just believes you:
>
> rhaas=# create table foo (a serial primary key);
> CREATE TABLE
> rhaas=# create table bar (a int, foreign key (a) references foo (a)
> not valid, check (a != 0) not valid);
> CREATE TABLE
> rhaas=# \d bar
> Table "public.bar"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer |
> Check constraints:
> "bar_a_check" CHECK (a <> 0) NOT VALID
> Foreign-key constraints:
> "bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
Didn't realize that marking constraints NOT VALID during table creation
was syntactically possible. Now I see that the same grammar rule for table
constraints is used for both create table and alter table add constraint.
> I suspect this is an oversight. We allowed FOREIGN KEY constraints to
> be not valid in 722bf7017bbe796decc79c1fde03e7a83dae9ada by Simon
> Riggs, but didn't add allow it for CHECK constraints until Alvaro's
> commit of 897795240cfaaed724af2f53ed2c50c9862f951f a few months later.
> My guess is that there's no reason for these not to behave in the same
> way, but they don't. Amul's proposed one-liner might be one part of
> actually fixing that, but it wouldn't be enough by itself: you'd also
> need to teach transformCreateStmt to set the initially_valid flag to
> true, maybe by adding a new function transformCheckConstraints or so.
So, any NOT VALID specification for a FK constraint is effectively
overridden in transformFKConstraints() at table creation time but the same
doesn't happen for CHECK constraints. I agree that that could be fixed,
then as you say, Amul's one-liner would make sense.
Thanks,
Amit
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2015-12-09 02:23:31 | Re: PostgresNode::_update_pid using undefined variables in tap tests |
Previous Message | Robert Haas | 2015-12-09 02:18:29 | Re: More stable query plans via more predictable column statistics |