Re: How to always run UPDATE FROM despite missing records in thesource table?

On 1/11/19 4:50 AM, Alexander Farber wrote:
> Good afternoon
> 
> I have prepared a simplified test case for my question: 
> https://www.db-fiddle.com/f/22jfWnsvqD8hVeFPXsyLbV/0
> 
> In PostgreSQL 10.6 there are 2 tables:
> 
> 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);
> 
> which are filled with the following test data:
> 
> 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 <http://1.2.3.0/24>', -33.4940, 143.2104),
>   ('10.0.0.0/8 <http://10.0.0.0/8>', 34.6617, 133.9350);
> 
> Then in a stored function I run the following UPDATE command -
> 
> 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;
> 
> (the 1 and the ip address are actually in_uid and in_ip parameters in my 
> stored function).
> 
> The above query works well and updates all 4 fields in the users table.
> 
> However the following query does not work as intended and does not 
> update any fields, because there is no matching block in the geoip table 
> found:
> 
> 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;
> 
> The field visited and ip however should be always updated - regardless 
> if the block was found or not.
> 
> Kind of LEFT JOIN, but for UPDATE - how to achieve this please?
> 
> The only workaround that I could think of is -
> 
> 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;
> 
> But that would run the same subquery twice (correct?) and my geoip table 
> is already slow with 3073410 records (and that is why I am trying to 
> cache its lat and lng values in the users table on each user login event)

Why not put a test for the block in the function and then use different 
UPDATE's depending on the result?

Pseudo code:

IF ip IN block THEN
    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;

ELSE

UPDATE users u SET
     visited = now(),
     ip      = '20.20.20.20'::inet
FROM geoip i
WHERE u.uid = 2 ;

END IF;

> 
> Regards
> Alex


-- 
Adrian Klaver
adrian.klaver@aklaver.com