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@xxxxxxxxxxx
