Пишите понятные запросы ("по смыслу"), не заморачивайтесь вещами вроде:

> фильтровать "перемноженные" данные будет сложнее ибо данных будет больше

Как и когда фильтровать — забота планировщика, не Ваша.

А ну ок. А он могёт?

Есть совет чтобы было не wrong?

Если хочется то можно план посмотреть и сравнить. Добавь перед запросом explain analyze и посмотри.

Да, конечно.

Процент — любое количество любых символов. Если строка всегда начинается с somemain, то достаточно процента справа.

Если Вы просто пишете функции, никакого управления транзакциями в них просто не нужно — сначала проверяете данные (и throw error, если что не так, например), далее выполняете работу.
А в общем, разделять проверки и обработку по разным транзакциям — это [грубая] ошибка (потому что между ними база-то может поменяться, и проверка уже "мимо").

Понимаете, у меня сейчас миграция с мс скл там внутри процедуры проверка потом явно открывается транзакция и закрывается, мне этот ад надо будет править на пг, а я пока в тупике как так провернуть

переосмысливайте все заново. даже с оракл на постгрис тяжело мигрировать, а с ms sql тем более

Эээ... подождите, что?! ;)
В MS SQL тоже нет "настоящих" вложенных транзакций, так что я как-то не понимаю, зачем это там делается (по идее, там подобное тоже почти никогда не нужно).
Вы можете показать какой-то пример?

всем привет
сталкивался ли кто-нибудь с ошибкой "FATAL:  expected password response, got message type 88" ?

Развернута следующая конфигурация
PostgreSQl 13, настроена потоковая репликация между тремя хостами
все соединения идут через PgBouncer 1.14 в транзакционном режиме и раскидываются на хосты через HAProxy

ошибка возникает в момент высокой дисковой нагрузки(среднее время ответа диска увеличивается до 2х миллисекунд)
в логах pgBouncer в этот же момент вижу "closing because: client unexpected eof"

клиентские соединения идут через JDBC
в логах клиентского приложения вижу: "HikariPool-2 - Failed to validate connection org.postgresql.jdbc.PgConnection@2f36d73a (This connection has been closed.)"

в pg_hba.conf для всех соединений стоит md5

аутентификаци пользователе на стороне pgBouncer выполняется через auth_query

Я делал запрос для удобного использользования, вынес в with, основная цель это сделать insert into table (column) values (select * from tmp_streets), но пока все крпшится

Вообще что-то такое делают, вроде:

WITH constant(x, y, z) AS (
    SELECT 'one', 123*423, now()
)
SELECT ...
  FROM constant
 CROSS JOIN ...

Но в Вашей ситуации какая выгода от этого?

Имхо, в клиентской бибилотеке Hikari есть пулер, который проверяет время от времени, живы ли его соединения. Попробуйте увеличить интервал этой проверки.

Возможно, для проверки валидности соединения оно использует сессионные переменные, и этот способ несовместим с pgbouncer

with tmp_streets as (
  select 
    ru, 
    en, 
    type, 
    (select id from localities where localities.name_translit = ps.city) as locality_id,
    (select id from districts where districts.name_translit = ps.district) as district_id
  from p_street ps 
)
--
--select * from tmp_streets;

insert into streets ("name", name_translit, street_type_id, locality_id, district_id) 
values (select ru, en, type, locality_id, district_id from tmp_streets)

Я хочу с одной таблицы инсертить в другую

это пробовали https://stackoverflow.com/questions/44298848/pgbouncer-error-closing-because-client-unexpected-eof-age-0?

Ну так я и не понимаю, чем данный вариант лучше, чем просто:

INSERT INTO streets ("name", name_translit, street_type_id, locality_id, district_id) 
SELECT ru, en, type, (
       SELECT id
         FROM localities
        WHERE localities.name_translit = ps.city
       ) AS locality_id, (
       SELECT id
         FROM districts
        WHERE districts.name_translit = ps.district
       ) AS district_id
  FROM p_street AS ps;

?