PL/pgSQL-Programmierung

Diese Agenda ist auch Bestandteil (4.-5. Tag) des Kurses

• Grundlagen der Sprache
  
  • Wo PL/pgSQL läuft - und wie es sich von SQL-Funktionen und Anwendungscode unterscheidet
  • Funktionen und Prozeduren: seit PostgreSQL 11 können Prozeduren selbst COMMIT und ROLLBACK ausführen
  • Blöcke mit DECLARE/BEGIN/END, Verschachtelung, Labels; einmalige Skripte mit DO
  • Typen und Variablen: %TYPE, %ROWTYPE, RECORD, Arrays, zusammengesetzte Typen
  • Parameterarten IN, OUT, INOUT, VARIADIC, Vorgabewerte und die Fallen der Überladung
  • SELECT ... INTO mit und ohne STRICT; FOUND und ROW_COUNT auswerten
  • Rückgaben: RETURN, RETURN NEXT, RETURN QUERY, SETOF, RETURNS TABLE
  • Kontrollfluss
    
  • IF und CASE
  • LOOP, WHILE, FOR, FOREACH sowie EXIT und CONTINUE
  • Cursor: implizit in der FOR-Schleife, explizit mit FETCH und MOVE
  • REFCURSOR an die Anwendung zurückgeben
  
  
• Dynamische Abfragen (dynamisches SQL)
  
  • EXECUTE ... USING
  • format() mit %I für Bezeichner und %L für Werte
  • Warum zusammengesetzte Zeichenketten keinen Schutz bieten
  • Fehler abfangen und melden (Fehlerbehandlung)
    
  • EXCEPTION-Blöcke mit SQLSTATE und SQLERRM
  • RAISE in den Stufen NOTICE, WARNING und EXCEPTION
  • Aussagekräftige Meldungen mit GET DIAGNOSTICS und GET STACKED DIAGNOSTICS
  • Die versteckte Subtransaktion im EXCEPTION-Block und ihre Kosten in Schleifen
  
  
• Planer, Sicherheit, Rechte
  
  • VOLATILE, STABLE, IMMUTABLE - wozu der Planer diese Einstufung braucht
  • Folgen einer falschen Einstufung für Laufzeit und Ergebnis
  • SECURITY DEFINER oder SECURITY INVOKER
  • search_path als Angriffsfläche und wie man ihn fixiert
  • Trigger
    
  • Zeitpunkte BEFORE, AFTER, INSTEAD OF; Zeilen- und Anweisungsebene
  • NEW, OLD und die TG_-Variablen
  • Übergangstabellen (REFERENCING ... TABLE) bei Massenänderungen
  • Constraint-Trigger mit DEFERRABLE - Prüfung erst beim Transaktionsende
  • Event-Trigger für DDL
  • Praxisbeispiel: Änderungsprotokoll per Audit-Trigger ohne doppelte Schreiblast
  
  
• Erweiterter Praxistag - Geschwindigkeit und Parallelität (optional)
  
  • Plan-Caching: generische und spezifische Pläne, plan_cache_mode
  • Weshalb Einzelanweisungen in Schleifen mit der Zeilenzahl um Größenordnungen langsamer werden
  • Schleifen durch mengenbasierte Formulierungen ersetzen
  • Messen mit EXPLAIN (ANALYZE, BUFFERS) und auto_explain
  • Warteschlangen mit FOR UPDATE SKIP LOCKED, Advisory Locks, Deadlocks vermeiden
  • Typische Fehlerquellen: veränderliche Funktionen in Indexausdrücken, stille Typumwandlungen
  
  
• Erweiterter Praxistag - Testen und Ausrollen (optional)
  
  • Protokollierung mit RAISE im Betrieb
  • Tests mit pgTAP und Laufzeitprofile mit plprofiler an der Übungsumgebung
  • Versionierung von Funktionen und die Tücken von CREATE OR REPLACE
  • Änderungen als Migration ausliefern