Now, let try the same example with the NO KEY option. To achieve this with SELECT FOR UPDATE in PostgreSQL, we could use the NO KEY option in the FOR UPDATE clause. While updating the non key column values, we could force the behavior to not hold any lock on the key columns. Rather we update only the non key columns like salary, phone number, address, etc. In majority of the cases, we actually do not need to update any key columns, In this case, the SESSION 2 transaction will wait until the SESSION 1 transaction is complete. Try to acquire the SHARE lock on the parent table's id column. In the above example we are actually updating the non key column( balance), by acquiring the lock on id column.Īs we hold the lock on primary key column id, when we try to insert any value into the child table, then it will Now, let us discuss about the FOR UPDATE behavior in PostgreSQL.īy default, when we use FOR UPDATE, it means that it is going to return the rows which are retrieved by the select statement by acquiring the lock on key columns. The reason for this waiting state is due to the PostgreSQL's FOREIGN KEY data validation process between the child and parent tables. Postgres=*# INSERT INTO child VALUES(10, now()) Īs you see in the above output, the insert operation on the child table is in waiting state.Įven though the FOR UPDATE clause does not exist in the SESSION 2, the transaction would still remain in a waiting state. Postgres=*# UPDATE parent SET balance=balance-2 WHERE id=10 Postgres=*# SELECT * FROM parent WHERE id=10 FOR UPDATE
BEGIN įollowing is the behavior observed when we run these two transactions concurrently. Now, consider that I have an another concurrent transaction which tries to insert the data into the child table. This is because, we have serialized the data access by using the FOR UPDATE clause.
If we are just going to run the above statements concurrently, then there should not be any deadlocks among the transactions. UPDATE parent SET balance=balance-2 WHERE id=10 SELECT * FROM parent WHERE id=10 FOR UPDATE The following is an example transaction on the parent table which is using the FOR UPDATE clause to avoid any deadlock problems for this specific transaction. CREATE TABLE parent(id INT PRIMARY KEY, balance INT) ĬREATE TABLE child(id INT REFERENCES parent(id) ON DELETE CASCADE, trx_timestamp TIMESTAMP) Parent table but not the after trigger (as expected/documented).To start with a simple scenario, let us consider the following example with two tables and let us assume that an application is running concurrent update operations on the parent table. It is visible in the before trigger on the Ok, here’s the answer: The parent row is not visible from either before orĪfter triggers on the child table. parent_id ) ) RETURN OLD END $$ LANGUAGE plpgsql CREATE TRIGGER tr_parents_bd_report_parent_id BEFORE DELETE ON parents FOR EACH ROW EXECUTE PROCEDURE report_parent_id () CREATE TRIGGER tr_parents_ad_report_parent_id AFTER DELETE ON parents FOR EACH ROW EXECUTE PROCEDURE report_parent_id () CREATE TRIGGER tr_children_bd_report_parent_id BEFORE DELETE ON children FOR EACH ROW EXECUTE PROCEDURE report_parent_id () CREATE TRIGGER tr_children_ad_report_parent_id AFTER DELETE ON children FOR EACH ROW EXECUTE PROCEDURE report_parent_id () INSERT INTO parents ( parent_id ) VALUES ( 1 ) INSERT INTO children ( child_id, parent_id ) VALUES ( 1, 1 ) INSERT INTO children ( child_id, parent_id ) VALUES ( 2, 1 ) DELETE FROM parents SELECT * FROM results What Happens parent_id ), ( SELECT COUNT ( * ) FROM children WHERE parent_id = OLD.
#Postgres on update cascade serial
CREATE TABLE parents ( parent_id INTEGER NOT NULL PRIMARY KEY ) CREATE TABLE children ( child_id INTEGER NOT NULL PRIMARY KEY, parent_id INTEGER NOT NULL REFERENCES parents ( parent_id ) ON DELETE CASCADE ON UPDATE CASCADE ) CREATE TABLE results ( result_id SERIAL PRIMARY KEY, table_name VARCHAR ( 10 ) NOT NULL, trigger_when VARCHAR ( 10 ) NOT NULL, parent_present BOOLEAN NOT NULL, children_present INTEGER NOT NULL ) CREATE OR REPLACE FUNCTION report_parent_id () RETURNS TRIGGER AS $$ BEGIN INSERT INTO results ( table_name, trigger_when, parent_present, children_present ) VALUES ( TG_TABLE_NAME, TG_WHEN, EXISTS ( SELECT 1 FROM parents WHERE parent_id = OLD.
0 kommentar(er)
