@pgsql

Страница 795 из 1062
Anton [Mgn, az09@osm]
09.05.2018
15:50:00
Писать и выполнять прямой sql по возможности

Вьюшкой например

Google
Yaroslav
09.05.2018
16:05:37
как правильно написать?
Вы же почти ничего не показали... Откуда у вас дубликаты-то берутся?

Andiskiy
09.05.2018
16:08:06
есть таблица Owners и есть Addresses у Owners есть address_id по сути я хотел бы такой запрос SELECT SUM(count) From addresses INNER JOIN owners .....

Котяй Негодяй
09.05.2018
21:05:30
Насоветуйте ER тул для постгре.

Amir
09.05.2018
21:11:25
Да, отдельная сущность. А еще есть комментарий к телефону. И нужно будет связвать с юриками и физиками
Когда у вас в таблице 100500*n записей, то выгоднее, с точки зрения оптимизации, телефон держать не в отдельной сущности, а в денормализованном виде

Во время джойна появляются дубликаты записей, я делаю .distinct, затем суммирую значения по столбцу так .sum :count в итоге я получаю такой sql SELECT DISTINCT SUM(count) AS count_id FROM .... и соответственно не правильное значение, то есть все дубликаты записей берет, подскажите пожалуйста, как можно это сделать? исключить дубликаты и потом суммировать оставшиеся записи. rails
Какие то джоины в вашем случае необходимы только для проверки факта существования такой связи, но так как содержат не одну запись, то так ВЫ и получаете: накручиваете не только дубликаты, но и сложность исполнения запроса, завышение использования шаренных буферов, увеличение времени работы CPU. Вам такие джоины необходимо просто в exists переместить

Yaroslav
09.05.2018
21:45:35
Когда у вас в таблице 100500*n записей, то выгоднее, с точки зрения оптимизации, телефон держать не в отдельной сущности, а в денормализованном виде
Для каких-то запросов, может быть, и выгоднее (а другие пострадают, почти наверняка). Но просто от количества записей это вряд ли зависит.

Amir
09.05.2018
21:51:14
это тонкая материя: баланс между нормализацией данных и денормализацией) сперва при проектировании стараются максимально все нормализовать, максимально растащить по отдельным таблицам... потом когда приходит что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны не проще ли просто заложить под эти данные jsonb поле в том же контрагенте?

я просто не понимаю зачем телефоны хранить в отдельной таблице) адреса да, т.к. по адресам есть ФИАС, есть КЛАДР, это удобно

хотя у вас может телефоны это основной вид деятельности, тогда да, спорить не буду)

Yaroslav
09.05.2018
21:59:09
это тонкая материя: баланс между нормализацией данных и денормализацией) сперва при проектировании стараются максимально все нормализовать, максимально растащить по отдельным таблицам... потом когда приходит что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны не проще ли просто заложить под эти данные jsonb поле в том же контрагенте?
Обычно это не вопрос баланса, а только вопрос оптимизации. Т.е. _логически_ модель должна быть нормализована. > что кроме телефона надо хранить еще емейл, два емейла, данные телеги, данные секретаря, еще данные секретаря...еще телефоны и телефоны Обычно добавляются соответствующие таблицы. > не проще ли просто заложить под эти данные jsonb поле в том же контрагенте? По-моему, не проще. Чтобы обеспечить consistence этого "поля", придётся приложить немало дополнительных усилий.

я просто не понимаю зачем телефоны хранить в отдельной таблице) адреса да, т.к. по адресам есть ФИАС, есть КЛАДР, это удобно
Потому что такова реляционная модель, которую в общем стоит использовать для моделирования в реляционных СУБД. ;)

Amir
09.05.2018
22:00:46
вам не страшно так мыслить на стадии планирования архитектуры бд, если у вас записей не больше 100тысяч-)

Google
Amir
09.05.2018
22:01:50
что все используют первую/вторую/треюю нормальную форму)

Yaroslav
09.05.2018
22:04:03
что все используют первую/вторую/треюю нормальную форму)
Да, вообще это, эээ... нормально. ;) И четвёртую, и пятую (даже шестую, иногда).

Amir
09.05.2018
22:04:49
Реляционная она не потому что там необходимо использовать эти формы

использование/не использование их это ваше право, но вы на больших объемах данных начнете деградировать на сложных выборках

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

телефон это как раз один из таких чихов

Yaroslav
09.05.2018
22:09:24
Реляционная она не потому что там необходимо использовать эти формы
Да, не поэтому. Но если СУБД не находится в высшей достижимой форме, в ней уже возможны (и, по моему опыту, скорее всего, будут) аномалии в данных. И это (должно быть, иначе зачем было использовать RDBMS) куда важнее какой-то там производительности.

Amir
09.05.2018
22:09:46
в постгресе давно уже используют возможности нереляционных моделей баз данных, причем даже обходият по скорострельности, вам лучше про них немного почитать

поле JSONB ооочень интересное)

Amir
09.05.2018
22:11:12
все ограничения это нормально настроенные триггеры, с ними не так сложно разобраться

Yaroslav
09.05.2018
22:11:21
вы устанете на каджый чих заводить таблицу...точнее устаните в них разбираться потом
Да пока удаётся... мне кажется, так даже понятнее / логичнее.

Amir
09.05.2018
22:11:21
плюс констрейны

Ярослав, вам успехов в проектировании бд, видимо оптимизация в вашем случае противопоказана)

или в ней нет необходимости

Yaroslav
09.05.2018
22:14:29
в постгресе давно уже используют возможности нереляционных моделей баз данных, причем даже обходият по скорострельности, вам лучше про них немного почитать
Да причём тут "скорострельность"... Это сравнение яблок с апельсинами, вот в чём дело. Т.е. JSONb без дополнительных телодвижений —- это отсутствие всяких проверок, и сравнивать с реляционной моделью такую реализацию "в лоб" некорректно.

все ограничения это нормально настроенные триггеры, с ними не так сложно разобраться
А зачем мне триггеры в тех случаях, когда результата можно добиться встроенными средствами?

Google
Amir
09.05.2018
22:16:56
в jsonb вы не сможете поместить два одинаковых телефона например) он поймет автоматом что это тоже самое

Yaroslav
09.05.2018
22:17:04
Ярослав, вам успехов в проектировании бд, видимо оптимизация в вашем случае противопоказана)
Денормализация "вслепую" (не под конкретные запросы, а из соображений вроде "телефон это как раз один из таких чихов") —- это отличный способ ДЕоптимизации, IMHO. ;)

в jsonb вы не сможете поместить два одинаковых телефона например) он поймет автоматом что это тоже самое
Да, но проблема в том, что так же "смело" я туда помещу вообще любой мусор, разве нет?

Amir
09.05.2018
22:22:59
Ярослав, еще планировщик умеет нормально просчитывать план выполнения запроса только для первых ЭНЦАТЬ (10 или 8, смотря что в настройках стоит) если вы в одном запросе используете много джоинов, а фильтр прийдет по 11му джоину, то вы получите полный seqscan всех первых 10 таблиц и только потом отфильтруете по 11й вот это не забывайте) а по jsonb:

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

но что плохого в муссоре) в этом как раз и вся прелесть)

Yaroslav
09.05.2018
22:25:12
по сути это такая же таблица) что и ваши телефоны, если вы в свою таблицу не хотите добавлять новых колонок, и никому этого не дадите, то муссор не появится.)
А как я "никому этого не дам"? Для базы-то JSONb поле (без constraint-ов) —- немногим более, чем обычный BLOB. Т.е. пиши что хочешь, да и ладно. ;) В отличие от реляционной модели, где поле —- это в любом случае какой-то domain (хотя бы и базовый).

Amir
09.05.2018
22:28:53
у меня был один коллапс по этому join_collapse_limit, только пример был сложнее: там по первой из джоина таблицы тоже было условие в where, и по 11й, планировщик стал плясать от первой, потому что в настройках было 10 но это было совсем неудачно) т.к. по 11й был однозначно единственный вариант

Yaroslav
09.05.2018
22:29:12
но что плохого в муссоре) в этом как раз и вся прелесть)
Я не вижу в мусоре никакой прелести, т.к. СУБД, по идее, должны использоваться для хранения каких-то важных данных, а не для мусора. ;)

Amir
09.05.2018
22:34:41
в нашем случае апдейтов вставок инсертов по отношению к выборке не так много, поэтому в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали пользователь не заметит если у него даже на 1 секкунду будет дольше сохранятся....но зато потом эти данные потом гораздо быстрее можно открыть на просмотр, анализировать, использовать в расчетах, выгрузках данных у вас скорее всего другие приоритеты: сделать бд максимально понятной архитектору бд, (возможно пользователи ваши тоже видят вживую эту бд и работают с ней напрямую)

>в нашем случае апдейтов вставок инсертов по отношению к выборке не так много это в процентном соотношении)

Артур
09.05.2018
22:40:49
Почему телефоны в отдельной таблице, а не в JSON Допустим: У Вас есть свой телефон и телефон мамы. Вы сообщили о контакте оба. Мама ваша (ничего про нее плохого не имею ввиду), указала свой телефон и Ваш, а еще Вашего брата. Брат ваш указала и сваой и в Мамы и Ваш телефон. Нужно: Пойнять что телефоны сходятся у нескольких пользователей. Нужно поссчитать количество быстро посчитать количество таких связей. В базе 1 000 000 контактов. У каждого указано по 3-4 телефона.

Yaroslav
09.05.2018
22:41:46
в нашем случае апдейтов вставок инсертов по отношению к выборке не так много, поэтому в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали пользователь не заметит если у него даже на 1 секкунду будет дольше сохранятся....но зато потом эти данные потом гораздо быстрее можно открыть на просмотр, анализировать, использовать в расчетах, выгрузках данных у вас скорее всего другие приоритеты: сделать бд максимально понятной архитектору бд, (возможно пользователи ваши тоже видят вживую эту бд и работают с ней напрямую)
> в нашем случае перекладывают некоторые расчеты на момент сохранения, что бы потом выборки летали Перекладывать _расчёты_ —- т.е. вычислять _производные_ данные на основании корректных —- это совсем другое дело. По сути, это такая же "денормализация", как, например, индексация. ;) Логически в этом случае модель нормализована, все constraint-ы поддерживаются (и если выкинуть все производные данные, ничего, кроме производительности, не изменится). А нарушение нормальных форм —- это "выкидывание" каких-то ограничений. > у вас скорее всего другие приоритеты Да, у меня приоритет —- корректность хранимых данных.

Артур
09.05.2018
22:44:31
Я про то, что в JSON это контрпродуктивно хранить

Amir
09.05.2018
22:45:14
кейс интересный)

Сергей
09.05.2018
22:45:29
Не юзайте json, если можете нормально нормализовать. Не ленитесь. Json для неструктурированных данных

Yaroslav
09.05.2018
22:45:43
Я про то, что в JSON это контрпродуктивно хранить
Более того, в JSON вообще в большинстве случаев контрпродуктивно хранить. Продуктивно в случае, если этот самый JSON(b) —- это такой "почти что blob". ;)

Google
Amir
09.05.2018
22:47:34
если у вас действительно такие кейсы то вы правы, я просто хотел предостеречь что на каждый чих не стоит заводить отдельные таблицы) в вашем случае телефоны это фундамент бизнеса)

но кейс этот инетересен и с точки зрения jsonb)

используйте номер телефона как ключ jsonb) значение : подмассив где есть имя коменты и тп

Артур
09.05.2018
22:53:05
Далее мое личное мнение Любые данные фундамент бизнеса. В итогом виде они либо отнимают время на перестройки механизмов и написание костылей по выборке. Либо сильно экономят время и позволяют сделать оптимизацию, исключив некоторые выборки из бэкенда. Я не могу представить ни одного кейса, когда бы нормаизация была бы неприемлемой. Денормализованные данные буду т приводить к избыточности алгоритмов, индексов и непрозрачности с точки зрения для тестирования. Единственный кейс, когда это приемлемо - долговременное хранение данных (типа логов) с поведением insert + select + delete

Yaroslav
09.05.2018
22:53:20
если у вас действительно такие кейсы то вы правы, я просто хотел предостеречь что на каждый чих не стоит заводить отдельные таблицы) в вашем случае телефоны это фундамент бизнеса)
А даже если не "фундамент", зачем ослаблять контроль? Цель какая? Я понимаю, если вы столкнулись с техническими ограничениями СУБД, которые не позволяют выполнить нормализацию, или эти телефоны каким-то образом создают проблемы с производительностью, и не очень важны (что уже странно)...

Артур
09.05.2018
22:55:39
Но изначально вопрос стоял несколько иначе ? "есть ли возможность повестить consraint на родительскую таблицу и чтобы при добавлениии записи в дочернюю (inherits) он не ругался что запись не найдена.?

Yaroslav
09.05.2018
22:58:12
Но изначально вопрос стоял несколько иначе ? "есть ли возможность повестить consraint на родительскую таблицу и чтобы при добавлениии записи в дочернюю (inherits) он не ругался что запись не найдена.?
И ответ "нет, это не работает". ;) Мой вам совет —- лучше не используйте наследование вообще (ни для чего, кроме партиционирования, и всяких "хитрых трюков").

Артур
09.05.2018
23:00:30
Цель: Нужен единый id для сделок. Есть ряд таблиц ссылающихся на одну и ту же таблицу и нужно контроллировать целостность (например телефоны ссылкаются на контакты,а это юрлица и физ лица, каждые со своими полями.)

Amir
09.05.2018
23:01:41
тут я уже немного запутался, inherits подразумевает что у вас все таки связь 1 к 1му, т.е. сущност КОНТРАГЕНТ_А может существовать только в одной записи и быть напрмиер дополненной в наследуемой таблице, если это есть, верно? т.е. если вы телефон выделяете в наследуемую таблицу, то вы не сможете две записи приравнять одному контрагенту

поправьте меня если я ошибаюсь)

Yaroslav
09.05.2018
23:06:03
Цель: Нужен единый id для сделок. Есть ряд таблиц ссылающихся на одну и ту же таблицу и нужно контроллировать целостность (например телефоны ссылкаются на контакты,а это юрлица и физ лица, каждые со своими полями.)
Ну, есть модель "общего предка" (забыл, как называется). Но поддерживать целостность в ней тоже не сахар (для полной реализации). :( Т.е. идея в том, что есть таблица "лица" (контрагенты), на которую уже ссылаются таблицы юр. лиц и физ. лиц., т.е. одна сущность представлена "частями" в _двух_ таблицах. Соответственно, таблицы, которые должны ссылаться на контрагентов вообще, ссылаются на таблицу контрагентов. Те же, которые должны ссылаться только, например, на юр.лиц, имеют FK на таблицу юр.лиц. (и т.п.).

Amir
09.05.2018
23:07:33
ну тут норм)

пока все логично)

привязываете телефон к головной таблице)

на "лица" привязываете телефоны и все там уже не надо ломать голову физ лицо это или юр лицо

Артур
09.05.2018
23:09:39
И нет. Для constraint оказывается: pk родительской таблицы! = pk дочерней

Amir
09.05.2018
23:10:26
т.е. один телефон может быть как у физ лица так и у фирмы

пусть будет проблем не вижу)

Google
Артур
09.05.2018
23:12:10
пусть будет проблем не вижу)
Ругается при вставке телефона. Говорит что нет такого pk в родителькой. Если insert был вставлен в дочернюю

Amir
09.05.2018
23:13:28
в общем вы чтото начудили в целостности данных

Yaroslav
09.05.2018
23:13:46
И нет. Для constraint оказывается: pk родительской таблицы! = pk дочерней
Ну да, такая вот реализация inherits. Планов по исправлению я что-то не помню / не видел. Вообще, мне кажется, всё это наследование в PostgreSQL пришло из времён увлечения "объектно-реляционными СУБД" (что бы это ни значило). Т.к. "не взлетело", улучшения в самом механизме сейчас никому не интересны (насколько мне известно).

в общем вы чтото начудили в целостности данных
Да нет там никакого чуда, это так и работает, к сожалению.

Amir
09.05.2018
23:15:24
у вас телефоны на какую таблицу как дочернюю ссылаются? Лица, физ лицо, юр лицо?

в общем в вашей модели не может телефон существовать без данных Юр лица/физ лица/лица вы вставили только телефон

сделали бы свои телефоны обычной таблицей и FK на эту таблицу и все)

т.е. вам надо отвязать наследование

Артур
09.05.2018
23:19:31
Чуть позже опишу еще один кейс. Может и в самом деле чтото не так делаю. Пока в дороге.

Amir
09.05.2018
23:19:53
пока у вас телефоны как наследуемая таблица, то вы не сможете на одно лицо повесить два телефона

Сергей
09.05.2018
23:20:20
можно еще использовать generic foreign key - хранить 2 колонки с чистом(id другой таблицы) и типом(таблицу, из какой тащить данные)

Yaroslav
09.05.2018
23:20:22
Чуть позже опишу еще один кейс. Может и в самом деле чтото не так делаю. Пока в дороге.
Да всё вы "так" делаете, просто это НЕ РАБОТАЕТ. См. https://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS А именно: Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

Сергей
09.05.2018
23:20:46
тогда телефон в отдельной таблице и его можно привязать к любой сущности

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

Amir
09.05.2018
23:22:37
как пример реализации партицирования до 10й версии, да можно

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

Страница 795 из 1062