Come posso disabilitare tutte le contraffazioni nel mio postgresql?
c# entity-framework-core postgresql
Domanda
Ho un database PostgreSQL con tabelle che hanno più di 500 miliardi di righe. Sto cercando di eliminare circa 6 milioni di righe ma ci vogliono più di 4 minuti. Ho bisogno di ottimizzare quelle operazioni di cancellazione per creare un sistema utile.
Penso che una buona idea potrebbe essere quella di disabilitare vincoli e trigger e quindi riattivarli, quindi le mie domande sono:
Come posso disabilitare tutti i vincoli? E 'possibile disabilitare le relazioni tra le tabelle?
In questo momento, disabilito solo i trigger usando questo:
Alter table "table_name" disable all
E riattivalo usando questo:
Alter table "table_name" enable all
modificare
Questo è il risultato della query con i trigger abilitati.
Delete on "ParameterValues" (cost=0.00..1015910.71 rows=47196359 width=6) (actual time=296995.938..296995.938 rows=0 loops=1)
Buffers: shared hit=6000000 read=420811 dirtied=93346 written=51646
-> Seq Scan on "ParameterValues" (cost=0.00..1015910.71 rows=47196359 width=6) (actual time=7977.126..19798.361 rows=6000000 loops=1)
Filter: ("Id" < 23000000)
Rows Removed by Filter: 34218414
Buffers: shared read=420811 dirtied=49228 written=51646
Planning Time: 304.085 ms
Execution Time: 296995.963 ms
Disabilitando tutti i trigger, ho migliorato le prestazioni riducendo i tempi di esecuzione da 5 minuti a 3 minuti.
Alter table "table_name" disable trigger all
Tuttavia, devo migliorare altre operazioni di eliminazione. Qualche idea?
Risposta accettata
L'istruzione corretta per disabilitare tutti i trigger su una tabella è
ALTER TABLE atable DISABLE TRIGGER ALL;
Ciò disabiliterà tutti i trigger e i vincoli di chiave esterna definiti nella tabella, poiché i vincoli di chiave esterna sono implementati dai trigger di sistema in PostgreSQL.
Disabiliterà inoltre la chiave primaria differibile , i vincoli univoci e di esclusione, anch'essi implementati con i trigger. Chiave primaria non differibile, vincoli univoci ed esclusivi non hanno trigger associati e non sono interessati.
In seguito è possibile riattivare questi vincoli abilitando i trigger, ma non sarà possibile verificare la coerenza. Poiché ciò è pericoloso, la disabilitazione di tali trigger richiede privilegi di superutente.
Un modo alternativo per disabilitare i vincoli basati su trigger è impostare il parametro session_replication_role su replica , che richiede anche i privilegi di superutente.
L'unico modo per disabilitare i vincoli di controllo e la chiave primaria non differibile, i vincoli univoci e di esclusione è eliminarli e successivamente ricrearli. Quest'ultimo richiederà del tempo poiché controlla la coerenza della tabella, ma potresti comunque vincere, poiché renderà più veloci le modifiche ai dati.
Per riferimento consultare la documentazione di ALTER TABLE .
Risposta popolare
Se vuoi una soluzione pl / pgsql pura. puoi usare un blocco anonimo come questo (o anche una funzione con nome di tabella e schema come argomenti).
DO $$
DECLARE
l_tab_name TEXT := 'yourtablename';
l_schema_name TEXT := 'public';
rec record;
BEGIN
for rec IN
(
SELECT rel.relname as table_name ,con.conname as constraint_name
FROM pg_catalog.pg_constraint con
INNER JOIN pg_catalog.pg_class rel
ON rel.oid = con.conrelid
INNER JOIN pg_catalog.pg_namespace nsp
ON nsp.oid = connamespace
WHERE nsp.nspname = l_schema_name
AND rel.relname = l_tab_name
)
LOOP
EXECUTE format ('ALTER TABLE %I
DROP CONSTRAINT %I',rec.table_name,rec.constraint_name );
END LOOP;
END $$;
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow