Обсуждение: 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
On Thu, Dec 5, 2024 at 3:06 PM Alvaro Herrera <alvherre@alvh.no-ip.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
On 2025-Jan-15, Amul Sul wrote: > 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. Yes to all of the above. But the complaint is not that the behavior is incorrect. The complaint is that users add NOT VALID and the code silently behaves as if the parameter was not given, which surprises them unpleasantly. The WARNING message is there to tell them "look, I realize you wanted to do this, but I know better, so I decided to ignore you." That's all. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Use it up, wear it out, make it do, or do without"