Подскажите, почему у меня очень долго работает функция, из-за чего

это может происходить? Функция это набор insert'ов и update'ов их штук 25, для удобства они объединены в эту функцию на plpgsql. PostgreSQL 9.6.
Происходит вот что, я вызываю эту функцию и на небольших исходных данных (они берутся по id из исходных таблиц) всё отлично работает. И вот тут настал момент когда исходных данных 70-160 тыс записей. Не так уж и много, но в определённый момент выполнение повисает и висит долго, меня хватало подождать 8 часов. Пробовал несколько раз.
Но вот если я выполняю все эти insert'ы и update'ы "руками" друг за другом, так же как они идут в функции, то всё работает и не так уж долго.

проверьте во время выполнения - нет ли висящих в базе незакомиченых транзакций от других сессий. Возможно какой-то апдейт ждет на блокировке строки которая залочена в соседнем окошке вашей IDE :)

благодарю за совет. делал чистую среду, ничего лишнего. делал systemctl restart и потом только я один запускал. сейчас думаю сделать несколько функций поменьше и вызывать их в в этой функции

Так вы выясните для начала на каком из запросов у вас 8 часов висит, длинные транзакции можно увидеть в pg_stat_activity в момент выполнения процедуры. так же в тело процедуры можно накидать raise notice с выводом шага и времени, для понимания где висит. Ну то есть ситуация явно ненормальная, надо найти корневую причину а не переписывать код наугад.

я давно выяснил, на каком операторе это происходит. да, использую RAISE NOTICE для этого. Но если я выполняю всё это по отдельности, в том числе и этот "косячный" UPDATE, то всё работает как надо.

ну тогда остается смотреть план выполнения этого update - в процедуре и в ручном режиме, статистику выполнения, ожидания блокировок, ожидания ИО чем-то же он занят :) установить расширение pg_stat_statements и смотреть на каких частях плана тупит

т.е. в общем нет разницы как запускать операторы - сами по себе или внутри функции?

разница есть. но вряд ли это имеет значение в вашем случае

Не совсем ясно что значит "сами по себе". Если все это внутри plpgsql процедуры, то по сути среда выполнения SQL оператора одна и та же, вызываете вы его внутри текущей процедуры или внутри вложенной в неё. Если у вас SQL скрипт, выполняющийся допустим в psql, а в нем есть вызовы процедур, то могут быть нюансы меняющие план выполнения запроса между выполнением в скрипте и выполнением внутри процедуры. Если выполнение SQL вызывается приложением через интерфейс драйвера, то это третья история и скорее вопрос к приложению и тому как оно взаимодействует с драйвером.. В любом случае можно начать с того чтобы проконтролировать план выполнения запроса когда он тормозит и когда не тормозит. Если план разный то надо принять меры к его фиксации в нужном варианте. А если план один и все равно тормозит, то это либо проявляются какие-то блокировки, либо...какие-то нюансы работы plpgsql о которых я пока не знаю :)

вот кстати пример такого "нюанса" работы plpgsql: https://stackoverflow.com/questions/58079898/postgresql-how-to-run-query-in-parallel-in-function описан случай когда параллелизм запроса работает вне plpgsql и не работает внутри функции, из-за того что функция возвращает табличный тип

изначально эту функцию вызывало приложение. теперь оно выключено и только я один под одной ролью в этой БД. сами по себе - это просто скрипт с теми же самыми операторами, что и в функции, но параметр передаваемый в функцию ($1) заменён на реальное значение использую DataGrip

А в скрипте просто перечень операторов или все обернуто в транзакцию? Autocommit в соединении для скрипта включен?

И второй вопрос почему набор из insert и update в функции на plpgsql?

а где он должен быть?

Вопрос почему на plpgsql а не sql

многие логику помещают внутрь процедур/функций

Я понимаю, только если функция на plpgsql а тестируется логика функции в скрипте с подставкой параметра то тест нерелвантный

"руками" выполняете в рамках одной транзакции или каждый statement сам по себе коммитится?

тут я согласен, что тест нерелевантный. но помещать код внутрь процедур/функций нормально

у меня используется переменная - географическая область интереса, она используется для уменьшения выборок и чтобы каждый раз ее не находить, я сохраняю ее в переменную. а так да, я могу и просто sql-функцию использовать

сам по себе

оберните в транзакцию и выполните всё вместе - по идее должна воспроизвестись ошибка с зависанием

буду пробовать

В SQL-функциях же нельзя использовать команды управления транзакциями? у меня постгрес 9.6. только plpgSQL же

Вам предлагают в скрипте сделать явную транзакцию

Да, я про ручной запуск через DataGrip или как там это всё запускается.

да любая функция это уже транзакция, надо только уровень изоляции задать. Но проблема не в этом, а в блокировках. Надо смотреть кто кого ждет.

В plpgsql функциях тоже нельзя явно управлять транзакцией, только в процедурах или анонимных блоках кода.

Функция это не транзакция. Не пишите глупости, пожалуйста.