Re: CREATE TABLE NOT VALID for check and foreign key

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.

Thanks,

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"We're here to devour each other alive" (Hobbes)