Thread: CREATE TABLE NOT VALID for check and foreign key
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?
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)
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
Hi, On Mon, Dec 23, 2024 at 10:10 AM 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. I'd like PostgreSQL to raise errors and/or warnings for the NOT VALID check constraint for CREATE TABLE. Ruby on Rails supports creating check constraints with the NOT VALID option and I was not aware that it is just ignored until https://github.com/rails/rails/issues/53732 issue is reported. Rails has implemented a kind of workaround by not dumping the NOT VALID option, but it does not help for the first execution. https://github.com/rails/rails/pull/53735 Thanks, -- Yasuo Honda
Hello, On 2025-Jan-07, Yasuo Honda wrote: > I'd like PostgreSQL to raise errors and/or warnings for the NOT VALID > check constraint for CREATE TABLE. > Ruby on Rails supports creating check constraints with the NOT VALID > option and I was not aware that it is just ignored until > https://github.com/rails/rails/issues/53732 issue is reported. Thanks. I left a comment there. I think raising a WARNING might work. > Rails has implemented a kind of workaround by not dumping the NOT > VALID option, but it does not help for the first execution. > https://github.com/rails/rails/pull/53735 Yeah, that's probably not ideal. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "You're _really_ hosed if the person doing the hiring doesn't understand relational systems: you end up with a whole raft of programmers, none of whom has had a Date with the clue stick." (Andrew Sullivan) https://postgr.es/m/20050809113420.GD2768@phlogiston.dyndns.org