Leider hat Postgres keine eingebaute Flashback Funktion. Für Flashback Database könnte man sich als Alternative ein Komplettbackup der DB erstellen (z.B. mittels pg_basebackup)
Im Fehlerfall würde man dann mit einem zeitbasierten Recovery wieder auf den gewünschten Zeitpunkt zuückstellen.
Wir möchten uns heute jedoch eine Alternative für Flashback Transaktion mittels eines Update und Delete Trigger basteln:
Beispieltabelle erstellen:
CREATE TABLE scott.emp (
empno numeric CONSTRAINT PK_EMP PRIMARY KEY,
ename character varying(10),
job character varying(9),
mgr numeric,
hiredate date,
sal numeric(7,2),
comm numeric(7,2),
deptno numeric(2,0));
INSERT INTO scott.emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO scott.emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO scott.emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO scott.emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO scott.emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO scott.emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO scott.emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO scott.emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-6-1987','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO scott.emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO scott.emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO scott.emp VALUES(7876,'ADAMS','CLERK',7788,to_date('13-6-1987','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO scott.emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO scott.emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO scott.emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
CREATE TABLE scott.emp_flashback AS SELECT * FROM scott.emp
WHERE 1=2;
Für die Flashbacktabelle werden drei weitere Spalten hinzugefügt
ALTER TABLE scott.emp_flashback
ADD column xid INT,
ADD column zeit TIMESTAMP,
ADD column aktion char(1);
Ein Trigger in Postgres verwendet immer eine seperate Procedure oder Function:
CREATE OR REPLACE PROCEDURE emp_trg_proc()
AS $$ DECLARE
dml_type CHAR(1);
BEGIN
CASE TG_OP
WHEN 'UPDATE' THEN
dml_type:='U';
ELSE
dml_type:='D';
END CASE;
INSERT INTO scott.emp_flashback(empno,ename,job,mgr,hiredate,sal,comm,deptno,xid,zeit,aktion)
VALUES (old.empno,old.ename,old.job,old.mgr,old.hiredate,old.sal,old.comm,old.deptno,txid_current(),now(),dml_type);
END $$ LANGUAGE plpgsql;
Nun wird der Trigger erstellt:
CREATE TRIGGER emp_trg
AFTER UPDATE OR DELETE
ON scott.emp
FOR EACH ROW
EXECUTE PROCEDURE emp_trg_proc();
Hinweis: Wenn Sie einen Trigger wieder löschen möchten, verwendet man folgende Syntax:
DROP TRIGGER emp_trg ON scott.emp;
Beispieltransaktion:
update scott.emp set sal=sal+1 where deptno=10;
Prüfen der Flashback-Tabelle:
select empno,sal,zeit from scott.emp_flashback;
Jetzt kommt der spannende Teil: Welchen Wert hatt eine Gehalt zu einem bestimmten Zeitpunkt in der Vergangenheit mittels unserer selbst gebauten Flashback Funktion:
with flash as (select '2021-08-02 08:30:00'::timestamp as back)
select empno,sal from scott.emp_flashback
where zeit=(select max(zeit) from flash,scott.emp_flashback where zeit<flash.back)
UNION ALL
select empno,sal from scott.emp
where empno not in (select empno from scott.emp_flashback
where zeit=(select max(zeit) from flash,scott.emp_flashback where zeit<flash.back));
Wenn Sie die Flashback Funktion für Ihre Umgebung nachbauen möchten, müssen Sie hier den Primärschlüssel empno durch Ihren Primärschlüssel austauschen.
Weitere tolle Tipps erhalten Sie in einem von unseren Postgres Kursen.