- Postgres on update cascade how to#
- Postgres on update cascade update#
- Postgres on update cascade plus#
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.
Postgres on update cascade plus#
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?
Postgres on update cascade update#
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. This will not work, however, because although the update on id will cascade to totals (effectively renaming the row as intended), the trigger will still fire, causing a recalculation and yielding errors because id on totals have changed before the trigger was run.I have an existing foreign key that has ON DELETE NO ACTION defined. The most natural way would be add a foreign key with on cascade update into totals. I would like to do better: somehow make the change in id in keys propagate to totals as well. Now, if for whatever reason we allow id in keys to change (that is, we want to "rename" an entity, and preserve all foreign key associations in other tables), the above will still work however the database will recalculate the sums for the entire subset of rows which has its id changed in values (and leave a row with a total sum of zero for the previous id). With the above, editing values in any way (inserting, updating, deleting) will trigger calc_totals() and the totals table will be kept consistent. If not new is null then return new else return old end if Ĭreate or replace trigger calc_totals_triggerīefore insert or update or delete on valuesįor each row execute function calc_totals() Insert into totals values (new.id, new.value) on conflict (id) do update
Update totals set total = totals.total - old.value where id = old.id We do this by using a trigger such as: create function calc_totals() The goal here is to have totals contain the sum of all values for every key. Keeps track of the sum of all values associated with an id. Consider the following: - Contains the ids.Ĭonstraint values_fk foreign key (id) references keys(id) on update cascade This is better explained with an example.
A foreign key whose value in the original table can change, but we want to avoid unnecessary calculations in that case (that is, we want the change to cascade without recalculating everything somehow).A trigger function used to keep a certain consistency calculation in a table.
Postgres on update cascade how to#
I have a question on how to elaborate a schema in PostgreSQL which involves:
0 kommentar(er)
