Re: How to always run UPDATE FROM despite missing records in the source 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(dot)klaver(at)aklaver(dot)com