Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TABLE NOT VALID for check and foreign key

CREATE TABLE NOT VALID for check and foreign key

From
jian he
Date:
hi.
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.

CREATE TABLE s6(id bigint , CONSTRAINT con1 check(id > 1) not valid);
src2=# \d s6
                Table "public.s6"
 Column |  Type  | Collation | Nullable | Default
--------+--------+-----------+----------+---------
 id     | bigint |           |          |
Check constraints:
    "con1" CHECK (id > 1)

create table pk (a int, primary key(a));
create table fk (a int, b int, foreign key(a) references pk( a) not valid);
                 Table "public.fk"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
 b      | integer |           |          |
Foreign-key constraints:
    "fk_a_fkey" FOREIGN KEY (a) REFERENCES pk(a)

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?

Re: CREATE TABLE NOT VALID for check and foreign key

From
Alvaro Herrera
Date:
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)

Re: CREATE TABLE NOT VALID for check and foreign key

From
Kirill Reshke
Date:
On Thu, 5 Dec 2024 at 14:36, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> 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.
+1 on that

-- 
Best regards,
Kirill Reshke