DBA: Synchronisierung und Import kompetent organisieren

1. Wohin versenden?

1.1. Temporäre Tabellen (VORÜBERGEHENDE TABELLE)

Eigener Namespace für jede Verbindung zur Datenbank

"Selbstzerstörung" mit Unterbrechung

Nichtreplikation

1.2. Nicht protokollierte Tabellen (UNLOGGED TABLE)

• erstellte "seine" Tabellen mit maximal zufälligen Namen, um mit niemandem zu kreuzen
• Extrahieren : Daten von einer externen Quelle in sie gegossen
• Transformieren : transformiert, ausgefüllt mit Schlüsselbindungsfeldern
• Laden : Fertige Daten in Zieltabellen gegossen
• "meine" Tabellen gelöscht

. , , (. 29), . , ; . , . , , .

1.3. ON COMMIT {REIHEN LÖSCHEN | FALLEN}

1.4. WIE ... EINSCHLIESSLICH ...

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

2. Wie schreibe ich?

3. Wie gehe ich damit um?

• Sie haben in Ihrer Datenbank eine Platte mit Kundendaten für 1M-Datensätze
• Jeden Tag sendet Ihnen der Kunde ein neues vollständiges „Bild“.
• Aus Erfahrung wissen Sie, dass sich von Zeit zu Zeit nicht mehr als 10.000 Datensätze ändern

3.1. Vollständiger Synchronisationsalgorithmus

• lösche alles was nicht mehr ist
• Aktualisieren Sie alles, was bereits war, und Sie müssen aktualisieren
• füge alles ein, was nicht war

LÖSCHEN VON dst

• delete ( DELETE) überhaupt
• füge alles aus einem neuen Bild ein

TRUNCATE dst

• clear ( TRUNCATE) die ganze Tabelle
• füge alles aus einem neuen Bild ein

• Wir starten eine lange Transaktion
• TRUNCATElegt AccessExclusive -Lock fest
• Wir machen die Einfügung für eine lange Zeit, und alle anderen zu diesem Zeitpunkt können nicht einmalSELECT

ALTER TABLE ... RENAME ... / DROP TABLE ...

• AccessExclusive auch , wenn auch wesentlich kürzer
• Alle Abfragepläne / Statistiken dieser Tabelle werden zurückgesetzt. ANALYZE muss ausgeführt werden
• Alle Fremdschlüssel (FK) brechen auf dem Tisch

LÖSCHEN, AKTUALISIEREN, EINFÜGEN

--     ,     "" 
BEGIN;

--      
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES --    ,   
) ON COMMIT DROP; --       

-- -     COPY
COPY tmp FROM STDIN;
-- ...
-- \.

--  
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) --   
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- ""

--  
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); --   

--  
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Nachbearbeitung importieren

--  
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

--    
CREATE TABLE kladr$log(
  ro kladr, --      /
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

--    
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  --      
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  --   
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

3.3. Verwandte Sets importieren

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

0123456789;;A-01;2020-03-16;1000.00
9876543210;;A-02;2020-03-16;666.00
0123456789;;B-03;2020-03-16;9999.00

INSERT INTO invoice_import
VALUES
  ('0123456789', '', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', '', 'A-02', '2020-03-16', 666.00)
, ('0123456789', '', 'B-03', '2020-03-16', 9999.00);

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
--   SELECT DISTINCT,    
  client_inn inn
, client_name "name"
FROM
  invoice_import;

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

--     ID   
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

--       ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL --  ID  
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

--  ID    
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; --