Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Почему в crosstab нужно указывать VALUES в запросе на выборку?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    https://www.postgresql.org/docs/current/tablefunc....
    The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

    crosstab(text, text) вариант не требует именно values. Второй аргумент функции может быть любым запросом, который сгенерирует полный список категорий. Этот вариант crosstab нужен, если первый запрос может возвращать данные не для всех категорий.

    С точки зрения синтаксиса, insert .. values - это то же самое, что insert .. select, а values - это частный случай select только списка констант
    melkij=> VALUES ('кофты'), ('ботинки'), ('пальто');
    column1
    ---------
    кофты
    ботинки
    пальто
    (3 строки)

    это самодостаточный запрос. Можно записать как select 'кофты' union all select 'ботинки' union all select 'пальто', но зачем?
    Ответ написан
    1 комментарий
  • Почему пропадает вывод таблицы в терминале psql?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    По-умолчанию, psql делегирует вывод размером больше вашей консоли в утилиту пагинации, сконфигурированную в вашей системе. Например, more или less. Дальнейшее поведение соответственно на усмотрение этой программы.
    \pset pager off выключает pager
    Ответ написан
    1 комментарий
  • Какой будет более правильный вариант при секционировании таблиц?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Можно объявить PK индивидуально на каждой партиции. Глобально-уникального индекса всё равно нет.
    Составной PK включающий ключ партицирования логичным образом изменит своё поведение.

    PS: в чём вы предполагаете смысл от BY HASH ("created_at")?
    PPS: под пользователями понимаются не люди? Просто людей на Земле маловато чтобы имело смысл делать партицирование по пользователям.
    Ответ написан
  • Как исправить несоответствие версии сортировки?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Специальный варнинг, очень явно подсказывающий, что держать физическую репликацию на разных ОС - идея плохая. Причина в время от времени происходящих изменении правил сортировки строк: https://wiki.postgresql.org/wiki/Locale_data_changes
    СУБД это задевает в части индексов, если на мастере было '1-1' < '11', то запись запишется в одно место btree, а если на реплике оператор сравнения говорит что '1-1' > '11', то выполнение запроса смотрит в логичное для этого результата место индекса и вообще не находит данные. Потому что мастер их записал располагая другим ответом функции сравнения строк.
    Так и проявляется IRL, поиск по индексу говорит что данных нет, но если форсировать seqscan - то данные находятся.

    Варнинг на мой взгляд действительно избыточно говорливый, сыпется при каждом подключении к базе, но какой есть. Обновляйте ОС, в общем, на одинаковую в рамках всего кластера. Затем перестраивайте пострадавшие индексы.
    Ответ написан
    Комментировать
  • Должен ли тип данных внешнего ключа совпадать с типом данных первичного ключа?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    serial - это и есть integer, а типа данных serial вообще не существует. Это просто синтаксический сахар вокруг неявного создания sequence из времён, когда GENERATED ALWAYS AS IDENTITY не было ни в стандарте ни в postgresql.

    Обратно к вопросу: тип данных может не совпадать. Но обычно это ошибка схемы данных, нежели запланированное различие.
    Ответ написан
    Комментировать
  • Как понять к какой БД относиться таблица из pg_stat_io?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Это всегда current_database()
    У каждой БД свой собственный системный каталог со своим собственным pg_class, pg_namespace и так далее (за исключением буквально пары действительно глобальных вроде pg_database и pg_authid).
    Ответ написан
    1 комментарий
  • Как настроить prepared statement в pgbouncer?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    PHP PDO несовместим с max_prepared_statements фичей баунсера.

    PDO отправляет deallocate как SQL запрос, а не как команду протокола.
    Ответ написан
  • Как можно получить изменения во VIEW, когда изменяется базовая таблица?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    view не хранит никаких данных. Это просто сохранённое дерево запроса, встраиваемое в обратившийся к этой view запрос.
    Вам нужен триггер на таблице с данными, который будет писать в отдельную аудит таблицу историю изменений. Потом из этой таблицы получайте историю.
    Ответ написан
    Комментировать
  • Как партиционировать большую таблицу в PG 11?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    если clock это unixtime, а так же именно по нему и хотим партиционировать в дальнейшем:

    begin;
    set local statement_timeout to '100ms';
    alter table history add constraint partition_key check (clock < :N) not valid;
    commit;
    alter table history validate constraint partition_key;
    begin;
    set local statement_timeout to '100ms';
    alter table history rename to history_old_data;
    create table history (...) partition by range (clock);
    alter table history attach partition history_old_data for values from (minvalue) to (:N);
    create table history_part_... partition of history for values from (:N) to (...);
    commit;


    :N - некая дата в будущем, до неё данные будут писаться в старую 5тб табличку, после - в новую партицию. Если до выбранного :N не успеете завершить миграцию - то удалить check constraint, иначе запись, конечно, встанет.
    Ответ написан
    2 комментария
  • Как уменьшить время выполнения запроса like '%uri%' в Postgresql?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    create extension pg_trgm;
    create index concurrently db_url_trgm_idx on db1 using gin ("Url" gin_trgm_ops);

    https://www.postgresql.org/docs/current/pgtrgm.html
    Ответ написан
    Комментировать
  • Как задать дефолтные права на все таблицы в схеме в postgres?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Необходим свой комплект ALTER DEFAULT PRIVILEGES для каждого пользователя, который будет создавать объекты в базе.
    Это раз.

    Два - идея вообще стабильно работать будет только для CRUD. Всякие alter table требуют быть владельцем таблицы либо быть в группе владельца (не в общей с владельцем группе, а именно чтобы владелец таблицы был в группе пользователя).

    Подумайте над организацией процессов. Наименее проблемно будет, если у вас будет только один пользователь, который создаёт или меняет схему данных, а ro (select только) и rw (select/insert/update/delete) пользователей может быть много. Всё остальное к приключениям.
    Ответ написан
    Комментировать
  • Как транспонировать строки в столбцы?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    проще на клиенте форматировать. Но поскольку число столбов в этой задаче можно считать константой, то можно и захардкодить
    select EXTRACT(YEAR FROM time) AS year,
    sum(cost) filter(where EXTRACT('month' FROM time) = 1) as jan,
    sum(cost) filter(where EXTRACT('month' FROM time) = 2) as feb,
    ...
    sum(cost) filter(where EXTRACT('month' FROM time) = 12) as dec
    from tablename
    where ...
    group by 1
    order by 1
    Ответ написан
    Комментировать
  • Оптимизируется ли UPDATE, если значения полей на самом деле не меняются?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    В postgresql создаётся физически новая версия строки при update (с обновлением всех индексов либо hot update если применимо) даже если ни одно поле не меняет своё действительное значение. Есть встроенный триггер suppress_redundant_updates_trigger, который можно повесить на таблицу, тогда ничего действительно не меняющий в данных update будет пропускаться.

    Если меняется хотя бы одно значение - оптимизации не подлежит, должна быть записана новая версия строки в таблицу (чтобы MVCC обеспечить). Если возможно, оптимизация HOT update исключит сопутствующее обновление индексов.
    Ответ написан
    9 комментариев
  • PL/pgSQL. Не дает вернуть из функции значение типа setof record. Причина?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Вариант записи допустимый, а вот вызываете неверно:
    melkij=> create or replace function myfunc()
    returns setof record
    as $$
    declare
    res record;
    begin
    execute 'select 1' into res;
    return next res;
    execute 'select 2' into res;
    return next res;
    return;
    end;
    $$
    language plpgsql;
    CREATE FUNCTION
    melkij=> select * from myfunc() as s(i int);
     i 
    ---
     1
     2
    (2 строки)
    
    melkij=> select myfunc();
    ERROR:  materialize mode required, but it is not allowed in this context
    КОНТЕКСТ:  PL/pgSQL function myfunc() line 6 at RETURN NEXT


    PS: прямой return query (или return query execute если это реально необходимо) будет проще и нагляднее перекладывания return next через лишнюю переменную.
    Ответ написан
  • Можно ли восстановить дамп кластера postgresql-12 ( oc Ubuntu ) на postgresql 14 ( ос Alpine )?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Да
    Ответ написан
    Комментировать
  • Удаление базы данных postgresql без запущенного демона?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Без запущенного экземпляра postgresql не предусматривается никакого эквивалента drop database.

    Можно удалить весь PGDATA и восстановить из бекапа весь кластер (если бекапы бинарные aka pitr) либо инициализировать заново через initdb, запустить и импортировать дамп (если бекап представляет собой дампы).
    Ответ написан
    Комментировать
  • Какой уровень блокировки строк по умолчанию в запросе SELECT?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Ни один из перечисленных.
    Для простого SELECT нужны AccessShareLock на таблицу/индексы/etc. row-level блокировки не применяются за ненадобностью.
    Ответ написан
    Комментировать
  • Как сделать определение конфигурации поиска в другом столбце таблицы?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    melkij=> \df to_tsvector
                                      Список функций
       Схема    |     Имя     | Тип данных результата | Типы данных аргументов |  Тип  
    ------------+-------------+-----------------------+------------------------+-------
     pg_catalog | to_tsvector | tsvector              | json                   | функ.
     pg_catalog | to_tsvector | tsvector              | jsonb                  | функ.
     pg_catalog | to_tsvector | tsvector              | regconfig, json        | функ.
     pg_catalog | to_tsvector | tsvector              | regconfig, jsonb       | функ.
     pg_catalog | to_tsvector | tsvector              | regconfig, text        | функ.
     pg_catalog | to_tsvector | tsvector              | text                   | функ.
    (6 строк)

    действительно не существует такой функции.

    Поправьте тип данных поля config на ожидаемый regconfig.
    Ответ написан
    Комментировать
  • Почему тип столбца xml не проверяет входное значение?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    melkij=> select 'hello'::xml;
      xml  
    -------
     hello
    (1 строка)
    
    melkij=> select '<foo>hello'::xml;
    ERROR:  invalid XML content
    СТРОКА 1: select '<foo>hello'::xml;
                     ^
    ПОДРОБНОСТИ:  line 1: Premature end of data in tag foo line 1
    <foo>hello
              ^
    line 1: chunk is not well balanced
    <foo>hello

    есть проверка.

    Если вам нужно проверить, что это не просто валидный xml, но целый документ - добавьте check constraint с проверкой на columnname is document
    Ответ написан
    2 комментария
  • Секционирование таблицы по хэшу. В чем принцип?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Как-как. Вызывается некоторая хэш-функция для значений ключа партицирования, её результат делится нацело на modulus, строка попадёт в ту партицию, для которой совпадает remainder.

    create table part (i int) partition by hash (i);
    create table part_0 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 0);
    create table part_1 partition of part FOR VALUES WITH (MODULUS 2, REMAINDER 1);
    insert into part select generate_series(1,10);
    select * from only part_0;
    select * from only part_1;


    на слишком малых выборках хэш ожидаемо может быть не столь равномерно распределён.
    Ответ написан
    Комментировать