1
votes

Comment toujours exécuter UPDATE FROM malgré des enregistrements manquants dans la table source?

J'ai préparé un cas de test simplifié pour ma question -

Dans PostgreSQL 10.6, il y a 2 tables:

UPDATE users SET
    visited = now(),
    ip      = '20.20.20.20'::inet,
    lat     = (SELECT lat FROM geoip WHERE '20.20.20.20'::inet <<= block),
    lng     = (SELECT lng FROM geoip WHERE '20.20.20.20'::inet <<= block)
WHERE uid = 2;

qui sont remplies avec les données de test suivantes:

UPDATE users u SET
    visited = now(),               -- HOW TO ALWAYS UPDATE THIS FIELD?
    ip      = '20.20.20.20'::inet, -- HOW TO ALWAYS UPDATE THIS FIELD?
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 2 AND '20.20.20.20'::inet <<= i.block;

Ensuite, dans une fonction stockée, j'exécute la commande UPDATE suivante -

UPDATE users u SET
    visited = now(),
    ip      = '10.10.10.10'::inet,
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 1 AND '10.10.10.10'::inet <<= i.block;

(le 1 et l'adresse IP sont en fait in_uid et in_ip paramètres dans ma fonction stockée).

La requête ci-dessus fonctionne bien et met à jour les 4 champs dans le users code > table.

Cependant, la requête suivante ne fonctionne pas comme prévu et ne met à jour aucun champ, car il n'y a pas de bloc correspondant dans la table geoip trouvé:

INSERT INTO users (created, visited, ip) VALUES
  (now(), now(), '1.2.3.4'::inet),
  (now(), now(), '1.2.3.5'::inet),
  (now(), now(), '1.2.3.6'::inet);

INSERT INTO geoip (block, lat, lng) VALUES
 ('1.2.3.0/24', -33.4940, 143.2104),
 ('10.0.0.0/8', 34.6617, 133.9350);

Le champ visité et ip cependant doit toujours être mis à jour d - peu importe si le bloc a été trouvé ou non.

Genre de LEFT JOIN, mais pour UPDATE - comment y parvenir s'il vous plaît?

La seule solution de contournement auquel je pourrais penser est -

CREATE TABLE users ( 
  uid SERIAL PRIMARY KEY,
        created       timestamptz NOT NULL,
        visited       timestamptz NOT NULL,
        ip            inet        NOT NULL,
        lat           double precision,
        lng           double precision
  );

  CREATE TABLE geoip (
        block   inet    PRIMARY KEY,
        lat     double precision,
        lng     double precision
);

CREATE INDEX ON geoip USING SPGIST (block);

Mais cela exécuterait la même sous-requête deux fois (correct?) et ma table geoip est déjà lente avec 3073410 enregistrements (et c'est pourquoi j'essaie de mettre en cache ses valeurs lat et lng dans la table users sur chaque événement de connexion utilisateur)

postgresql sql sql-update left-join postgresql-10

0 commentaires

3 Réponses :

1
votes

Ma suggestion (peut-être stupide) est d'ajouter u.uid = 2 OR (u.uid = 2 AND '20 .20.20.20 ':: inet << = i.block) insted de cela AND condition .. et peut-être changer lat = i.lat en lat = NULLIF (i.lat, 0)

0 commentaires

1
votes

Vous n'avez pas besoin de rechercher deux fois la grande table geoip :

-- start transaction
-- some stuff

UPDATE users u SET
    visited = now(),               
    ip      = '20.20.20.20'::inet 
WHERE u.uid = 2;  -- fast because is from pk

UPDATE users u SET
    lat     = i.lat,
    lng     = i.lng
FROM geoip i
WHERE u.uid = 2 AND '20.20.20.20'::inet <<= i.block;

-- more stuff
-- commit tx

5 commentaires

Cela fonctionnera sûrement, mais j'espérais y parvenir en une seule MISE À JOUR (abuser de la magie noire de PostgreSQL serait aussi bien pour moi)

Pourquoi? ___________

J'espère qu'une seule MISE À JOUR prendrait moins de temps que deux.

À mon avis, ce sont deux opérations différentes. De plus, la première requête n'a aucun coût (presque)

À mon avis, à l'intérieur d'une fonction, le coût de la première requête est négligeable.

0
votes

M. Andrew Gierth au pgsql-general a > liste de diffusion a fourni une réponse SQL uniquement:

UPDATE users u SET 
    visited = now(),
    ip = v.ip,
    lat = i.lat,
    lng = i.lng
FROM (VALUES ('20.20.20.20'::inet)) v(ip)
      LEFT JOIN geoip i ON (v.ip <<= i.block)
WHERE u.uid = 2;

0 commentaires