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

Поиск
Список
Период
Сортировка
От Kevin Brannen
Тема RE: How to always run UPDATE FROM despite missing records in thesource table?
Дата
Msg-id MN2PR19MB2575EA7D3089B640480B780FA4850@MN2PR19MB2575.namprd19.prod.outlook.com
обсуждение исходный текст
Ответ на How to always run UPDATE FROM despite missing records in the source table?  (Alexander Farber <alexander.farber@gmail.com>)
Список pgsql-general
Дерево обсуждения

From: Alexander Farber <alexander.farber@gmail.com>

  • 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)

 

Have you considered using a WITH clause to get the data so the query is only run once?
See section 7.8.2 at https://www.postgresql.org/docs/9.6/queries-with.html

Kevin

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

В списке pgsql-general по дате отправления: