Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Как создать тип перечисления?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Чтож, зависит от того что именно вы хотите сделать.

    PostgreSQL позволяет сделать полностью собственную реализацию типа данных. Которая будет себя вести так как вы захотите - но всю эту логику вы должны разработать сами на C. Потому у create type и довольно объёмная документация.

    range типы - это диапазоны. Например, дата бронирования номера гостиницы. Таблица бронирований, одно поле с диапазоном дат tsrange и exclude constraint - всё, база проконтролирует, что даты бронирования не пересекаются.

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

    что-то похожее на штатные типы данных, но с ограничениями - вы хотели найти не create type, а create domain:
    CREATE DOMAIN smallint_between_10and200 AS smallint CHECK(value >= 10 and value < 200);

    Всё, теперь в таблицах вы можете использовать тип данных smallint_between_10and200, в который тем не менее записать что-то не из диапазона 10..200 будет невозможно.
    Ответ написан
    1 комментарий
  • Как проверить пустое ли поле?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Оператор сравнения в plpgsql такой же как в SQL. is null для проверки является ли значение null и = для равенства.

    Для изменения вставляемой строки вы должны модифицировать переменную new, а не пытаться уйти в бесконечную рекурсию выполняя аналогичную вызвавшему триггер операцию.
    Ответ написан
    3 комментария
  • Насколько дорогая SQL операция NOT IN/NOT EXISTS?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    В таблице items 12 млн записей, в using_items 11 млн. Разумеется количество гипотетическое и маловероятное.

    Да нагенерируйте и посмотрите explain analyze. Были бы действительно объёмы, а не всего-то десяток миллионов строк.

    not in дорог из-за требования стандарта по части обработки NULL в not in и отсутствии специальных оптимизаций для этого случая. Может быть оптимизация появится.
    not exists оптимален.
    через left join план может быть идентичным not exists
    Ответ написан
    Комментировать
  • PostgreSQL как посчитать количество ключей в JSON?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    select k, count(*) from tablename join  jsonb_object_keys(data) as k on true group by k

    ?
    Ответ написан
  • Как ускорить поиск?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Подумать, действительно ли нужен select *
    Если нет и нужно достать одно-два поля - запрашивайте только необходимые эти поля и добавьте их в индекс. В include для postgresql 11+ или в сам индекс после word. С учётом статичности самой таблицы будет index-only scan всегда.
    Если все нужны - то в принципе можно и в индекс вероятно загнать, но вряд ли в этом будет уже смысл.

    Можно ещё с hash-индексом попробовать если postgresql 10+. Но сомневаюсь.
    Ответ написан
    Комментировать
  • Как удалить строку из view таблицы в Postgresql?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Если материализованное view - то refresh materialized view
    Если не материализованное - то закешировано быть не могло. Смотрите определение view и исследуйте, откуда строка в резуультате этого запроса получается.
    Ответ написан
    Комментировать
  • Есть ли разница между выражением и транзакцией?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Можем ли мы быть уверены, что в следующей конструкции обновление обязательно произойдет (считаем что SELECT вернул какие-то данные)?

    Нет, не можем.
    Два конкурентных запроса могут увидеть одни и те же версии строк в cte (не пересекутся на читающих блокировках даже с конкурентными писателями т.к. mvcc) и затем пойдут обновлять одни и те же строки. На блокировках обновления строк их транзакции и сериализуются. Кстати, можно и deadlock словить если строк к обновлению будет несколько и они вернутся из запроса в разном порядке.

    приостановить запрос и посмотреть что будет, если произойдет X?

    gdb может приостановить всё что угодно. Правда далее уже интересный момент что надо найти куда именно break point ставить.
    Ответ написан
    Комментировать
  • Столбец удален, но не удален, как удалить столбец?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Мой хрустальный шар совершенно верно показал.
    Когда вы делаете add column columnname text - вы получаете NULL во всей этой колонке. Разумеется сразу после этого вы поставить NOT NULL не можете никак. Любая строка нарушает это ограничение и база отказывается ставить NOT NULL.
    Когда вы делаете add column columnname text NOT NULL - чтож, это может сработать в одном случае - у вас пустая таблица. Тогда ни одна строка не нарушает NOT NULL ограничение, у вас просто 0 строк. Если хоть одна строка есть - это невозможно. Вы просите default NULL и одновременно NOT NULL - так не бывает. В сообщении об ошибке говорится именно о добавляемой в этот момент колонке, не о какой-то другой существовавшей ранее.

    Вы должны указать какой-то не NULL default если хотите поставить NOT NULL на создаваемое поле. Либо очистить таблицу.
    Либо создать без NOT NULL, заполнить поле, потом уже делать NOT NULL.

    В postgresql действительно колонка не удаляется никогда. Но это деталь реализации и к сути вашего вопроса отношения не имеет.
    Ответ написан
    2 комментария
  • Какой есть аналог JSONB_OBJECT_KEYS для значений?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    json_each / jsonb_each
    Ответ написан
    Комментировать
  • Зачем нужен DEFERRABLE INITIALLY DEFERRED в результате sqlmigrate для миграции добавления модели?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Ваш сгенерированный SQL невалиден для postgresql. Ключевого слова AUTOINCREMENT не существует.

    А про deferred constraints:
    cc=> begin;
    BEGIN
    cc=> insert into blog_post (author_id) values (100);
    INSERT 0 1
    cc=> commit;
    ERROR:  insert or update on table "blog_post" violates foreign key constraint "blog_post_author_id_fkey"
    ПОДРОБНОСТИ:  Key (author_id)=(100) is not present in table "auth_user".

    Непосредственно insert отработал. Именно из-за deferred ограничения.
    Если вы проверяете вне транзакции - то insert будет транзакцией сам по себе и потому нет разницы
    Ответ написан
  • PostgresSQL не работает idle_in_transaction_session_timeout?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Но сессии в статусе idle, не удаляюся(

    idle != idle in transaction
    Для сурерпользователя настройка действует тоже. Сделайте begin; и подождите. Через указанный таймаут сессия будет убита (при том, клиентское приложение возможно это заметит только при попытке отправить запрос)
    Ответ написан
    Комментировать
  • Может кто дать совет по PostgreSQL?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Пропущены настройки autovacuum и bgwriter. Поведение на ненастроенный автовакуум в общем-то похоже. Плюс возможно у вас открытая транзакция болтается и дополнительно мешает. Либо неактивный слот логической репликации.

    WAL: поскольку чекпойнт говорит 0 removed, 0 recycled - значит кто-то мешает. Это могут быть:
    неактивные replication slot - проверяйте pg_replication_slots
    ошибки archiver - проверяйте pg_stat_archiver, archive_mode, archive_command
    wal_keep_segments

    swap - см. vm.swappiness
    Ответ написан
  • Не могу создать процедуру в PostgreSQL. Что делать?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Проверьте вашу версию postgresql.
    Синтаксис CREATE PROCEDURE и CALL присутствуют только в postgresql 11 и новее.
    Ответ написан
    Комментировать
  • Как организовать синхронную репликацию БД PostgreSQL на географически удаленных серверах?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Берёте и настраиваете.
    По настройкам сети - любые извращения, лишь бы у вас был открыт tcp порт (тот на котором слушает ведущий, 5432 обычно) от реплики к ведущему.
    За синхронность репликации отвечает synchronous_standby_names при включенном synchronous_commit

    Учтите что синхронная репликация при географическом распределении - больно. Именно жирным шрифтом. Как по производительности, так и по стабильности работы на запись данных - если чуть что с сетью - то ведущая база у вас ничего писать не будет пока не почините сеть. Потому что синхронная репликация.
    Ответ написан
    Комментировать
  • Помощь в расшифровки логов postgresql?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Не "оператор" потому что а statement - и будет сразу немного понятнее о чём речь.

    Первый delete пришёл через extended protocol так же известный как prepared statements. Рядом помимо execute должны быть ещё parse и bind.
    Второй - через simple query протокол, то есть просто текст запроса.
    Ответ написан
    2 комментария
  • Есть ли способ спросить у PostgreSQL, с какими запросами у него возникают наибольшие сложности?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Поставить pg_stat_statements, спустя какое-то время посмотреть что тот собрал. Например, нашим отчётом
    Ответ написан
    1 комментарий
  • Как указать доктрине, какой неймспейс использовать?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Слово namespace здесь неверно.
    Ищите по schema name и конкретно search_path

    Странно что вы не знаете что такое search_path, но он у вас модифицирован. В дефолтную настройку схема public включена и потому оба ваших запроса обычно делают одно и то же.

    В доктрине начиная с 2.5 схема указывается так:
    /**
     * Clerk
     *
     * @Table(schema="schema")
     */
    class Clerk { }

    Не уверен что будет работать для public схемы если доктрина предполагает что оно в search_path есть и так.
    Ответ написан
    1 комментарий
  • Как сделать репликацию PostgreSQL и распределение нагрузки с учётом нагрузки от записи, а не чтения?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Внимание вопрос: зачем?

    100 записей в секунду и 5 чтений в минуту - это ни о чём.
    Писать 10000 записей в секунду - ну и? Никакой проблемы держать на одной железке и это в обычном OLTP, а не 99% write/1% read как у вас.

    Взять адекватные диски, выкинуть к чертям докер. А для hot standby масштабировать запись не нужно. Простая и надёжная потоковая реплика.

    монго можно разделить на X нод без шаманских танцев с бубном

    Спасибо, хорошая шутка.

    На всякий случай: вы ведь поминаете что никакая репликация для масштабирования записи не может помочь? Если каждый хост должен содержать копию данных - значит он должен все данные записать. Для масштабирования записи - шардирование верное слово. Только это не про сотни транзакций записей.
    Ответ написан
    1 комментарий
  • Настройка Sequelize. Какое количество соединений?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    Настраиваете мониторинг, запускаете проект. Правите настройки или сам проект по ситуации.
    Знаю системы где пяти коннектов базы хватает на сотни транзакций в секунду. Знаю системы где полсотни коннектов постоянно заняты непойми чем.
    Ответ написан
    Комментировать
  • Как найти причину падения \ замедления работы сервера с PostgreSQL?

    Melkij
    @Melkij
    DBA Team для вашего PostgreSQL?
    весьма мощный сервер (8gb

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

    На тяжелые запросы не грешу, т.к., во-первых, билд запросов на 100% отдан ORM (Eloquent, Lumen), и возможность кривыми руками написать какой-нибудь count(*) сведена к нулю

    Ха. Хахахаха. Хорошая шутка.
    Самый лютый бред вместо запросов делают именно ORM.

    Выкручивать настройки логирования, в момент проблемы смотреть хотя бы вот это: https://github.com/dataegret/pg-utils/blob/master/...
    Ставить какой-то мониторинг, умеющий мониторить базу.
    И смотреть что именно делает база и чем занято железо в момент проблемы. Особенно I/O. process title бы хоть сохранили/написали. Там и то продублирован command tag.
    Ответ написан
    Комментировать