Pr sooner or later, many face the need to make bulk corrections in table records. I have already , and how — not to do it better. Today, I will talk about the second aspect of bulk updating — trigger execution.
For example, on a table where you need to make some adjustments, there is a nasty trigger ON UPDATE, transferring all changes to some aggregates. But you need to update everything (initialize a new field, for example) so carefully that these aggregates are not affected.
Let's just disable the triggers!
BEGIN;
ALTER TABLE ... DISABLE TRIGGER ...;
UPDATE ...; -- this takes a long time
ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;Actually, that's it — everything is already hanging.
Because ALTER TABLE nameće AccessExclusive-lock, under which no one running in parallel, not even a simple SELECT, will be able to read anything from the table. That is, as long as this transaction is not finished, everyone wishing to even 'just read' will wait. And we remember that UPDATE we have a loooong...
Then let's quickly disable it, then quickly enable it!
BEGIN;
ALTER TABLE ... DISABLE TRIGGER ...;
COMMIT;
UPDATE ...;
BEGIN;
ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;Here the situation is already better, the wait time is significantly reduced. But two problems spoil the entire beauty:
ALTER TABLEit still waits for all other operations on the table, including lengthy onesSELECT- While the trigger is turned off, 'any changes' in the table will 'fly by' , not even ours. And it won't hit the aggregates, although it should. What a pity!
Session variable management
So, in the previous option, we encountered a fundamental issue — we need to somehow teach the trigger to distinguish between 'our' changes in the table and 'not ours'. To allow 'our' ones as is, and to activate on 'not ours'. For this, we can use .
session_replication_role
Reading :
The firing mechanism of triggers is also influenced by the configuration variable . Triggers enabled without additional instructions (by default) will fire when the replication role is — 'origin' (by default) or 'local'. Triggers enabled by specifying
ENABLE REPLICA, will only fire if the current session mode — 'replica', and triggers enabled by specifyingENABLE ALWAYS, will fire regardless of the current replication mode.
I must emphasize that the setting does not apply to all at once, as ALTER TABLE, a samo do naše specijalne veze. Dakle, da ne aktiviraju nikakvi aplikativni okidači:
SET session_replication_role = replica; -- isključili okidače
UPDATE ...;
SET session_replication_role = DEFAULT; -- vraćeno u prvobitno stanjeUslov unutar okidača
Međutim, gornja opcija radi za sve okidače odjednom (ili je potrebno "alterovati" unapred okidače koje ne želite da isključite). A ako nam je potrebno "isključiti" jedan konkretni okidač?
U tome nam pomaže :
Imena parametara ekstenzija se zapisuju na sledeći način: ime ekstenzije, tačka i zatim samo ime parametra, slično punim imenima objekata u SQL-u. Na primer: plpgsql.variable_conflict.
S obzirom da van-sistemskih parametara može biti postavljeno u procesima koji ne učitavaju odgovarajući modul ekstenzije, PostgreSQL prihvata vrednosti za bilo koja imena sa dve komponente.
Prvo doradimo okidač, otprilike ovako:
BEGIN
-- procesu konverzije se može raditi sve
IF current_setting('mycfg.my_table_convert_process') = 'TRUE' THEN
IF TG_OP IN ('INSERT', 'UPDATE') THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END IF;
... Inače, to se može uraditi "u hodu", bez zaključavanja, preko CREATE OR REPLACE za funkciju okidača. A zatim u specijalnoj vezi postavljamo "svoju" promenljivu:
SET mycfg.my_table_convert_process = 'TRUE';
UPDATE ...;
SET mycfg.my_table_convert_process = ''; -- vraćeno u prvobitno stanje
Znate li neke druge načine? Podelite u komentarima.
Izvor: habr.com
