Invoice_typeid | integer | | | | plain | | Quote_detailsid | integer | | | | plain | | Post_code | integer | | not null | | plain | |īusiness_phone | text | | | | extended | |Īlternative_phone | text | | | | extended | | +-+-+-+-+-+-+-Ĭlientid | integer | | not null | generated always as identity | plain | |Īccount_name | text | | not null | | extended | |Īddress | text | | not null | | extended | | with the following specs: crewdb=#\d+ client Ĭolumn | Type | Collation | Nullable | Default | Storage | Stats target | Description Everything is already transactional.I have a table called client which depends on input from other tables. You also don't have to wrap any single statement in a txn - PostgreSQL isn't MySQL. ALTER TABLE bar VALIDATE CONSTRAINT bar_a_fkey ĮRROR: insert or update on table "bar" violates foreign key constraint "bar_a_fkey"ĭETAIL: Key (a)=(2) is not present in table "foo". The constraint still can't be validated (as shown below), but for the purposes of cascaded deletion all things are good. the lack of validation on bar means it still has a row that is invalid.It has nothing to do with cascading, observe CREATE TABLE foo VALIDATION is for the rejection of creating an FOREIGN KEY when the referenced rows do not exist. No, because the second you add the NOT VALID it applies to all rows inserted AFTER the statement as if they were always there. They'll only be checked during validation AFTER you tell it to validate, so you can delay that until you have scheduled downtime.īecause the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data? I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked. But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys and they'll fail unless the new row matches the specified check constraints). If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. The docs say this about NOT VALID ADD table_constraint Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data? The advantage of this approach is that the ACCESS EXCLUSIVE lock is held for a very short time for dropping/adding the constraint and then for validating the constraint only a SHARE UPDATE EXCLUSIVE on posts and ROW SHARE lock on blogs since I'm on Postgres 9.5.Īre there downsides to this? I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked. I know that I can perform 2 transactions to help with the check taking a long time: begin Īlter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid Īlter table posts validate constraint posts I need to perform an online migration (I don't have a dedicated downtime window). So, adding the foreign key blocks all access to the posts table for a decent amount of time because the lock is held while constraint validation occurs. Dropping/adding the foreign key acquires an ACCESS EXCLUSIVE lock on posts. The problem is that the posts table is large (4 million rows) which means that validating the foreign key can take a non-trivial amount of time (I've tested this with a copy of the database). I can do this within a transaction: begin Īlter table posts drop constraint posts_blog_id_fkey Īlter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade I need to change this foreign key to ON DELETE CASCADE. I have an existing foreign key that has ON DELETE NO ACTION defined.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |