Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Можно ли обновлять композитные типы в Postgres?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Эти две команды именно про enum:
    ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
    ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value

    Вот удаления значения из enum нет.

    Команды ADD/DROP/ALTER/RENAME ATTRIBUTE там же - это как раз про композитные типы. Уже используемые менять можно.
    Ответ написан
    Комментировать
  • Почему PostgreSQL ругается?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    А почему не должен ругаться?
    Ни малейших представлений что это за синтаксис такой.
    Ответ написан
    Комментировать
  • Как узнать почему autovacuum не запускается для некоторых таблиц?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    удаляется мало, добавляется пачками ( где то 10 миллионов в день )
    не удаляется и не обновляется ничего

    Так и что тогда vacuum'ить? Нечего, мёртвых строк нет же.

    insert'ы до pg13 не провоцируют приход автовакуума (autovacuum_vacuum_insert_scale_factor), да и это было добавлено не ради вакуума, а ради freeze, чтобы потом autovacuum to prevent wraparound было меньше работы.
    Ответ написан
  • Есть ли штатный способ разбить большой UPDATE на транзакционные части?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Как делить одну большую операцию на части - необходимо базе объяснять.

    PG14 - нагенерировать запросов
    update ... where ctid between '(N,1)' and '(N+1000,1)'

    где N от 0 до relpages из pg_class и запустить.

    Если хочется - можно обернуть в анонимку DO, каждую очередную итерацию делая commit.

    На более старых версиях - выбрать какой-нибудь индекс скоррелированный с физическим расположением данных и идти вдоль него, нагенерировав запросов
    update ... where somecolumn between ? and ?
    кусками некоторого размера. Не имеет решающего значения соблюдение размера одного обновления, лишь бы выполнялся за разумное время.

    Во время миграции поглядывайте, успевает ли autovacuum за вами, успевают ли реплики и archiver (если есть).
    Ответ написан
    Комментировать
  • Как изменить директорию для postgresql wal?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    У initdb и pg_basebackup есть ключи запуска для этого.
    А на уже существующей базе - остановить базу, скопировать wal'ы в нужное место, заменить директорию wal'ов в PGDATA симлинком на новое место, запустить базу.
    Ответ написан
    1 комментарий
  • Как определить источник обращение к БД?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Используйте более подробный log_line_prefix. В частности, нужен %h

    log_line_prefix = '%m %p %u@%d from %h [vxid:%v txid:%x] [%i] '

    Мы такой используем если нет значимых причин для иного.
    Ответ написан
    1 комментарий
  • Как решить ошибку "string indices must be integers" в postgresql?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    В postgresql такого текста ошибки нет.

    Этот запрос висит вечно

    Значит другие транзакции не дают вам получить access exclusive lock. (note: пока такой alter table в очереди ожидания - проект лежит)
    Посмотрите в pg_stat_activity что у вас запущено. например вот так
    Ответ написан
  • Как правильно составить запрос INSERT в postgresql?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    ... DO UPDATE SET payment_date = info.payment_date + interval '23 hours';

    Данные конфликтующей строки доступны
    The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

    имени таблицы (или алиаса указанного в запросе) - та версия строки что есть
    псевдоним excluded - те значения что этот insert предполагал записать
    Ответ написан
    2 комментария
  • Правильно ли организовано логирование?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Документация отвечает однозначно
    Current date and time (start of current transaction);

    current_timestamp - это всегда время начала транзакции. Сколько бы транзакция ни заняла времени, до её окончания current_timestamp будет возвращать одно и то же значение.
    А правильно это для вашей задачи или нужно использовать другую из функций датывремени - это только к вам вопрос.
    Ответ написан
    1 комментарий
  • Как ускорить работу слота репликации на PostgreSQL?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Скорей всего упираетесь в производительность IO на временных файлах, увеличение logical_decoding_work_mem с этим должно помочь.
    Затем посмотрите, не упираетесь ли в 100% cpu на этом процессе. logical decoding однопоточный, ему важна производительность в один поток. Тут ничего не поможет. Прочитать, понять и переупорядочить данные среди гигабайтов wal'ов всё равно придётся.
    Ответ написан
    Комментировать
  • Почему я могу установить NULL в колонку mark, если у меня есть CHECK(mark in (1,2,3))?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Нет, не должен. Значение NULL не противоречит такому check constraint. На самом деле даже в исходниках самого postgresql есть замечания, что такое поведение несколько неожиданно, но таков стандарт SQL.
    Ответ написан
    Комментировать
  • Как удалить null из выборки?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    array_agg(attr.name) filter(where ...)
    Ответ написан
    2 комментария
  • Как поменять значение max_stack_depth в postgres?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Сначала внимательно посмотрите на свой триггер, не пускаете ли вы его в бесконечную рекурсию. Нет, postgresql не будет вам мешать делать бесконечно-рекурсивный триггер и никак не будет препятствовать его выполнению до тех пор пока это будет возможно физически. И вот тут stack depth limit обычно и заканчивается первым.
    Ответ написан
    3 комментария
  • Как вставить аргумент функции между $$ $$ строкой в PostgreSQL?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Напрямую запросом, без лишнего execute
    CREATE OR REPLACE FUNCTION example(arg int)
    RETURNS int4
    LANGUAGE plpgsql
    AS $$
    declare
       out int;
       BEGIN
       --function body
       SELECT field * arg into out FROM randomtable;
       return out;
       END
       $$;
    select example(1);


    Или, если действительно нужно генерировать динамический SQL, то передать параметром в using:
    CREATE OR REPLACE FUNCTION example(arg int)
    RETURNS int4
    LANGUAGE plpgsql
    AS $$
    declare
       out int;
       somequery text;
       BEGIN
       --function body
       somequery := $q1$ SELECT field * $1 FROM randomtable $q1$;
       EXECUTE somequery into out using arg;
       return out;
       END
       $$;
    Ответ написан
    Комментировать
  • Соблюдается ли очередь данных при нескольких bulk-insert операциях?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Могут ли их primary key id смешиватЬся?
    То есть, если запросить дату с ORDER BY id, и получить

    Да.

    За генерацию значений отвечают sequence (неважно каким именно способом объявлена таблица - default nextval явно созданного сиквенса, serial или generated identity - это всё будет сиквенс).
    Сиквенс гарантирует только уникальность своих значений. Никаких гарантий порядка записи в таблицу он не даёт.

    Два параллельных insert в postgresql так же друг друга никак не блокируют и исполняются конкурентно. Кто и в каком порядке надёргал nextval сиквенса потребное для своих данных количество раз - такие значения они и запишут.
    Ответ написан
    Комментировать
  • Какой разрядности счётчики транзакций в PostgreSQL 14 версии?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    internals 64 битные
    heap 32 битные

    Если вы хотите чтобы ни один крупный проект не стал обновляться на такую новую версию - без проблем, поменять формат страницы heap не так уж сложно. Почему после принятия такого патча никто не станет обновляться? Потому что вместе со сменой формата страницы вы напрочь сломаете in-place pg_upgrade и вынудите обновляться только через dump+restore. Удачи это сделать на 10тб базе за обычные для pg_upgrade 5-10 минут даунтайма. А если у вас крошечная база которую можно за 10 минут обновить дампом - то вам и 64-битные xid банально не нужны и не важны.
    Если интересно поподробнее, подождите запись вот этого свежего доклада
    Ответ написан
    Комментировать
  • Есть ли разница в БД Postgres: хранить string(255) или string?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Для начала, в postgresql нет типа данных string. (а если это самодельный тип, что postgresql сделать тоже позволяет, то смотреть надо как он реализован у вас)

    Для текстовых данных в postgresql есть:
    - text. Просто text, без специфических ограничений на содержимое
    - varchar - это всё тот же самый text. К varchar можно добавить указание ограничения максимальной длины хранимого текста в символах. Почему это отдельный тип данных? Потому что такого требование стандарта SQL
    - char - фиксированный длины (в символах! в байтах они всё равно динамической длины получатся для того же utf8). Смысла его использовать в postgresql, как вы сами процитировали документацию, нет. Зачем он нужен? Потому что описан в стандарте.

    В отличии от, например, mysql, для postgresql нет ничего особого в значении 255. Если в определении таблицы видно такое ограничение на 255 символов - обычно это означает именно "никто не обдумывал цель такого ограничения для этого поля, просто какое-то число"

    Говоря о выборе между text или varchar(N) - varchar(N) с разумным для этих данных N предпочтительнее для коротких строк, потому что не даст записать 10мб туда где должно быть, например, не более 64 символов (это не совсем шутка, случай из практики). А по ошибке в месте записи дебажить логику приложения куда как проще, чем искать потом откуда такое взялось.
    Ответ написан
    2 комментария
  • Возможно подключить через PG Admin к удаленной БД на продакшене?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    а зачем нужен pgadmin для управления базой?

    Ну а помимо этого, pgadmin не только умеет подключаться удалённо куда ему скажут, но и самостоятельно запускать для этого ssh туннель.
    Ответ написан
    Комментировать
  • Как оптимизировать update postgresql?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    UPDATE orders_new
    SET client_code=access.title
    FROM orders_new O

    Ну классика жанра. Если не видите self-join - то посмотрите ещё раз. Очевидно ошибочный запрос.

    https://www.postgresql.org/docs/current/sql-update.html
    Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
    Ответ написан
  • Какую базу и способ хранения выбрать?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    База на 30гб? Какая смешная кроха.

    Да сгенерируйте себе синтетических данных и поиграйтесь с ними. Я понимаю неудобно на локальной машине щупать базу в десяток террабайт - но лишь только 30гб не проблема просто сгенерировать и посмотреть вживую.
    Ответ написан
    1 комментарий