On 2015/12/09 5:50, Robert Haas wrote:
> On Thu, Dec 3, 2015 at 3:52 AM, amul sul <sul_a...@yahoo.co.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);
> rhaas=# create table bar (a int, foreign key (a) references foo (a)
> not valid, check (a != 0) not valid);
> 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.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to