JOIN как в ORM: связи по foreign key в PostgreSQL

[erogov]

К слову, GROUP BY ALL уже почти с нами. А вот с SELECT * EXCLUDE не все так однозначно.

[iamkisly]

Если честно не совсем понял чего вам не хватало. Код стал куда более контринтуитивным, потому что часть логики потенциально спрятана от читающего запрос.

[mvv-rus]

Мне тоже непонятно. SQL - он вообще в другой парадигме сделан, в реляционной, в которой результат - просто множество, неупорядоченное и какой-то предопределенной навигации просто нет: если не заказывать сортировку записей через ORDER BY то они возвращаются в неопределенном порядке.Так, вообще-то сделано специально: чтобы реализовывать запросы по произвольным путям. А индексы и первичные.внешние ключи - это уже оптимизация поверх реляционной модели. Если же нужно ходить по предопределенным связям, то под это издавна есть другая альтернатива - сетевая модель. Она, столь же древняя (если не древнее), чем реляционная, но зумеры не так давно её открыли заново и теперь она известна под названием графовой NoSQL модели. Если надо ходить по предопределенным путям, то почему бы не пользоваться этой сетевой/графовой моделью, а не насиловать реляционную БД. Ну, а ещё есть средства генерации запросов SQL, знающие про связи через внешний ключ (да хоть те же ORM), и почему бы не использовать их вместо того, чтобы навешивать лишние возможности.

[gBear]

… первичные.внешние ключи - это уже оптимизация поверх реляционной модели.

Это, мягко говоря, вообще не так.

Если же нужно ходить по предопределенным связям …

Так “проблема” как раз в том, и заключается, что - в подавляющем большинства случаев - именно ЭТО и нужно. А “запросы по произвольным путям” ™ - это такая узкая ниша… необходимость, которой никто не отрицает, кстати… но синтаксис в SQL есть только для них.

Об чем, собственно, и статья.

[asmm]

в статье же указано чего всем не хватило за всю историю развития SQL

• Многословие. Пять связей — пять ON, слово в слово пересказывающих схему.

• Тихие ошибки. ON примет любое равенство: ON d.id = di.item_id вместо document_id выполнится молча — типы совпали. Ошибка всплывёт данными, а не компиляцией.

• Потеря смысла. По ON a.x = b.y не видно: связь это или совпадение, кто родитель, размножатся ли строки в агрегате.

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

[XelaVopelk]

Пять связей — пять ON, слово в слово пересказывающих схему.

На больших нагруженных базах FK обычно сносят нафиг в угоду производительности.
Есть другие минусы, но как по мне это основной.

[iamkisly]

Более того на больших базах их и не думали создавать. У меня сейчас в работе несколько БД.. и они никогда не видели первичных ключей, связей и тд. Просто потому что половина данных ежедневно или ежечасно перегружается с нуля из внешних систем

[asmm]

Без первичных ключей это не база данных, это файл, это набор данных. Вся реляционная модель построена на ключах и связях. Реляция - связь!

[iamkisly]

Ну значит вы никогда понастоящему не работали с данными. Потому что реляции по ключам реальны только там где они существуют с первого дня, а в жизни все немного иначе. В жизни есть дубли, есть поврежденные и фрагментированные данные. Данные которые вы получаете, но не управляете. У нас основная бд с которой работает подразделение получает данные с оборудования через xml дампы и принтауты консольных команд. Каждый день потенциально новый тип у существующей колонки, и появление новых. Никаких первичных ключей или признаков уникальности не предусмотрено в принципе. Максимум этой базы - синтетические id которые корректны только от аплоада до аплоада.

Тоже самое с картографическими данными коих у нас великое множество

[gBear]

Это всё - к сожалению, наверное - никак не отменят того факта, что без PK/FK и прочих ограничений это всё что угодно, но не реляционная модель.

“Дата лейки” не просто так придуманы, наверное… но и строятся они по другим принципам. И “реляционными” их никто не называет.

[mvv-rus]

Реляция - связь!

Нет, relation исходно, в реляционной алгебре - это отношение. То, что в промышленных СУБД именуется таблицей (table).

[asmm]

Сомнительное утверждение. Тут я обычно цитирую Томаса Кайта с тестами доводами и опытом:

Эффективное проектирование приложений Oracle / Это база данных, а не свалка данных

https://www.rsdn.org/article/db/goodoraapp.xml#EZTAE

потеря 10% производительности при записи, очень редко когда стоят достоверности всего набора. Это исключительные таблицы, которые потом ложатся в реляционную модель.

[XelaVopelk]

Это база данных, а не свалка данных

Конечно в серийных продуктах обычно обстоит лучше, но вот 25 летний опыт общения с корпоративными системами "собственной разработки" говорит мне, что уже лет через 5 после создания, первоначальные проектные решения уже начинают плохо биться с реальной жизнью. Хотя бы по банальной причине: бизнес фирмы сильно изменился. Это не говоря о том, что через эти 5 лет те самые проектировщики в фирме уже врят ли работают, а через 10 лет гарантированно не работают.

потеря 10% производительности при записи, очень редко когда стоят достоверности всего набора.

Во первых, 10 процентов это оптимистично, если связей не сильно много у таблицы. Но даже если и так если мы посмотрим на ситуацию, что мы можем выжать быстро целых 10% с RW ноды, которая горизонтально не масштабируется, это уже много и за эти 10% надо биться.

Во вторых, достоверность набора, это не только внешние ключи, это другие ограничения. То что мы одно из ограничений (FK) вынесем на уровень БД это не сильно нас спасёт, если мы начнём глать шлак в прод.

[iamkisly]

Проблема в том, что подход из статьи только увеличивает энтропию и количество обьектов.

Ответ: неотличимо от ручного джойна

Что характерно нет никакого этому подтверждения, потому что похоже в статье отсутствует план запроса до-после, или какой-либо бенчмарк. У джентельменов принято верить на слово, и если в pg в все именно так, то это замечательно, но к примеру в sql server использование методов.. смерти подобно потому что код в большинстве случаев не разматывается оптимизатором, как в случае вью или сахарных CTE.

не компонуется в цепочки вида client(document(di))

Лично мое мнение, как человека который последние 10 лет только и делает, что пялится в SQL.. за такое надо немного бить, может быть даже палкой. Во первых за то что это здорово снижает читаемость и понимание кода. Я абсолютно уверен, что пять одинаковых ON не многословность, а явное намерение.

Во вторых, за нейминг в приведенном примере. Табличные и скалярные методы опять же должны выражать намерение в названии.. а тут его нет. В третьих..

CREATE FUNCTION client_document_list(profile) RETURNS SETOF document LANGUAGE sql STABLE PARALLEL SAFEAS $$ SELECT * FROM public.document WHERE (client_id) = (($1).client_id) $$;

.. я не большой специалист в Pg, но это выглядит так словно мы одной нагой залезли в N+1 проблему. Делать это преимуществом по сравнению с JOIN.. ну такое. В принципе это не проблема для подможества, где использование LATERAL это норма.

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

Установка — одна команда

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

[asmm]

Что характерно нет никакого этому подтверждения, потому что похоже в статье отсутствует план запроса до-после, или какой-либо бенчмарк. У джентельменов принято верить на слово, и если в pg в все именно так, то это замечательно, но к примеру в sql server использование методов.. смерти подобно потому что код в большинстве случаев не разматывается оптимизатором, как в случае вью или сахарных CTE.

Ошибаетесь, всё необходимые EXPLAIN'ы выполнены и ссылки приведены и корнер кейсы расписаны. Вы видимо читали статью по-диагонали.

Вот эксплейны:

https://github.com/asmgit/pg_relation_sql/blob/main/EXPLAIN.md

Ссылка есть в статье

Корнер кейсы честно разобраны в разделе "Честные ограничения"

Про нейминг вы видимо тоже не прочитали.

.. я не большой специалист в Pg, но это выглядит так словно мы одной нагой залезли в N+1 проблему. Делать это преимуществом по сравнению с JOIN.. ну такое. В принципе это не проблема для подможества, где использование LATERAL это норма.

Опять же направляю Вас посмотреть EXPLAIN ссылку и почитать правила инлайна в PG (тоже есть в статье). На этой синергии и построен мой удивительный модуль.

Последние ваши изыскания совсем сумбурны. Если хотите донести мысль, поясните.

[mvv-rus]

в статье же указано чего всем не хватило за всю историю развития SQL

Вы таки уверены, что всем? Мне вот, к примеру SQL обычно хватало, причем - даже старого, который безо всяких JOIN, а с перечислением таблиц-источников в FROM и условиями их связи в WHERE.

Тихие ошибки. ON примет любое равенство: ON d.id = di.item_id вместо document_id выполнится молча — типы совпали. Ошибка всплывёт данными, а не компиляцией.

Это - не ошибка, это так изначально задумано: реляционная модель данных позволяет использовать любые связи между таблицами, а не только те, которые заданы в структуре БД.

Вообще, ваши претензии к SQL - это претензии к реляционной модели данных. Если вам не подходит по каким-то причинам реляционная модель (например, совсем совсем не нужна гибкость, но очень необхдима производительность), используйте другую модель данных и реализующую ее NoSQL.СУБД. Если вам сложно писать запросы SQL вручную - используйте средства автомтизированной генерации запросов. А PostgreSQL оставьте в покое. Или делайте свою СУБД на основе его хранилища (благо лицензия позволяет), но тогда уж - без претензий, что SQL - какой-то не такой.

[asmm]

Статья о том, что вся идеология SQL стремилась получить соединение по связям. Я же процитировал ниже свою же статью:

https://habr.com/ru/articles/1065684/comments/#comment_30295142

Мой подход не ограничивает, а даёт возможность использовать декларацию связей для соединения таблиц

[mvv-rus]

Статья о том, что вся идеология SQL стремилась получить соединение по связям.

Вы забыли после слов “идеология SQL” добавить оговорку “как я ее понимаю”. А понимание у вас - довольно специфичное: в исходной концепции реляционной парадигмы (основанной на реляционной алгебре, это такой раздел математики, и Кодд как раз работал в этой области) никаких определенных в структуре БД связей нет вообще, а есть отношения - наборы однородных записей-кротежей, данные из которых выбираются конкретным запросом. Связи - они были в другой парадигме организации БД, сетевой. И там они, кстати, были явные, с физическими указателями на списки связанных записей, а не неявные через сопоставление первичных и внешних ключей, как в современных БД, ориентированных на извлечение из них данных с помощью SQL. И языки запросов там другие, не SQL: например, был в той древности такой CODASYL.

Вы же хотите не просто использовать PostgreSQL как сетвую БД (в этом-то я не вижу ничего плохого), а ещё и сделать SQL языком для работы в сетевой парадигме, по крайней мере отсутствие в нем такой возможности вы считаете недостатком SQL. А это, по моему, плохо, потому что избыток возможностей перегружает язык, делает его чремерно громоздким, неудобным для изучения и работы (если чо, я и PL/1 помню, который страдал от этой самой перегрузки возможностями).
Если же, как у вас в статье, расширение сделано дополнительным пакетом, который в движок БД не встроен и который те, кому это не нужно, могут не использовать - это терпимо. А вот что касается рсширения самого языка (с доработкой его исполняющей системы), влияющее на всех, кому оно нужно и кому не нужно - лично я против. Собственно, только в этом и состоят мои возражения к статье. По поводу же вашего пакета расширения возражений не имею: пусть будет, глядишь, не только вам пригодится.

PS А для использования связей (через первичные/внешние ключи) IMHO лучше использовать автогенерацию запросов в той или иной форме, не трогая сам язык SQL.

[redfox0]

Не понимаю, что мешает написать так (план запроса такой же, что и с JOIN):

select
    i.name as item_name,
    p.name as client_name,
    m.name as manager_name,
    di.quantity
from
    document_item di,
    document d,
    item i,
    profile p,
    profile m
where
    d.id = di.document_id
and i.id = di.item_id
and p.client_id = d.client_id
and m.manager_id = d.manager_id

[asmm]

Видимо не внимательно статью читали:

Сокращать условия соединения SQL пытался не раз. Все четыре попытки — мимо.

Запятая + WHERE. Досахарная эпоха: таблицы списком, условия в WHERE вперемешку с фильтрами. Забыл условие — получил декартово произведение, молча.

[XelaVopelk]

Не понимаю, что мешает написать так

Когда я был лидом группы базистов я категорически запретил так писать, т.к. тут не видно какое условие к какому джойну относится и на ревью (или при изменении запроса "потом") кода это читать реально больно. Хорошо тут запросик короткий, а ежели он на весь экран.

Касательно вот такого синтаксиса.

FROM document_item, document(document_item), item(document_item)

Всё красиво, когда в запросе только он, но как только ты начинаешь подтаскивать данные не только через FK, начнётся плохочитабельная каша.
В это смысле ИМХО "лучше безобразно, но единообразно"

[asmm]

и опять комментатор, который статью не внимательно читал

Всё это лежит в каталоге, и по этим объявлениям идёт подавляющее большинство реальных соединений — один из практиков в обсуждении на HN оценил эту долю в «95%+ моих джойнов»

[iamkisly]

Если вы получаете примерно одинаковый негативный фидбек, то со статьей определенно что-то не то