Re: CREATE TABLE NOT VALID for check and foreign key

On Thu, Dec 5, 2024 at 3:06 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> Hello,
>
> On 2024-Dec-05, jian he wrote:
>
> > I found for foreign keys, check constraints,
> > you specify it as NOT VALID, it will not be marked as NOT VALID in the
> > CREATE TABLE statement.
>
> Uhmm, okay.
>
> > reading transformCheckConstraints, transformFKConstraints comments
> > appearingly this is intentional?
> >
> > If so, do we need to document the keywords "NOT VALID"
> > in create_table.sgml synopsis section?
>
> So, the whole point of ALTER TABLE adding constraints marked NOT VALID
> is to let the AccessExclusiveLock on the table be held for a very short
> time, without requiring a table scan; you follow that with ALTER TABLE
> VALIDATE to remove the marking, which takes a weaker lock. This is
> great for production-time constraint additions on large tables. But for
> CREATE TABLE there's no such argument: it's pointless to mark a
> constraint as NOT VALID, because nobody else could be looking at the
> table anyway.
>
> Maybe it would have been wise to forbid NOT VALID when used with CREATE
> TABLE. But we didn't. Should we do that now? Maybe we can just
> document that you can specify it but it doesn't do anything.
>

I might be mistaken, but I believe this behavior is reasonable since
we're creating a new table with no data initially. Future inserts will
be validated against the constraint, ensuring all data added complies
with it. Given that any data in the table at any time will be valid
according to the constraint, marking it as "VALID" seems like the
correct approach, IMO.

However, when a constraint is added via an ALTER TABLE statement, we
don't scan the table, so we can't be sure whether the existing data
complies with the constraint. In this case, marking the constraint as
"NOT VALID" makes sense.

I'm not sure if it's worth documenting or raising a warning about this.

Regards,
Amul