Как получить пустое множество в IN

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

Допустим надо проверить, что поле входит в какое-то множество. Очевидно, что надо использовать конструкцию типа

field IN (1, 2, 3, ...)

Но что если это множество пустое. Как его указать?

field IN (???)

Read more »

Как перенести объекты из одной схемы в другую

Этот вопрос довольно часто задают на форумах и в рассылке. Вот очередное такое письмо.

Стандартное решение проблемы:

ALTER TABLE table_name SET SCHEMA new_schema;
ALTER FUCNTION function_name SET SCHEMA new_schema;
-- и т.д.

Зачем CASCADE в DROP INDEX

Читая рассылку pgsql-general, cделал для себя интересное замечание. Вопрос был - для чего указывать CASCADE в DROP INDEX, что может зависеть от индекса? Ответ - если индекс UNIQUE, то FOREIGN KEY.

Источник тут.

Заметка про RENAME и триггера

В plpgsql триггерах удобно использовать RENAME для того чтобы абстрагироваться от NEW и OLD. Например:

IF TG_OP = 'DELETE' THEN
RENAME OLD TO myrow;
ELSE
RENAME NEW TO myrow;
END IF;

-- Далее работаем с myrow не задумываясь о типе триггера

Какие колонки таблицы входят в PK/FK

Вчера на sql.ru увидел вопрос - возможно ли при помощи запроса узнать какая колонка в таблице является PK а какая FK. Небольшое замечание к формулировке - в обоих случаях может быть несколько столбцов.

Собственно запрос получается такой:

SELECT
contype, -- тип ограничения (PK/FK)
attname -- имя атрибута
FROM pg_constraint
JOIN pg_attribute ON
attrelid = conrelid AND
attnum = any(conkey)
WHERE
contype in ('p', 'f') AND
conrelid = 'yourtablename'::regclass::oid
ORDER BY 1, attnum;

Локально работаем с PostgreSQL закрытом на сервере

Уже не в первый раз мне задают такой вопрос.

Как подключиться, например с помощью pgAdmin, к кластеру PostgreSQL, когда он где-то на сервере, где его порт доступен только локально, т.е. к нему закрыт доступ извне? При этом есть ssh на этот сервер, но по нему работать в консоли с psql и тестировать работающее с базой приложение очень не удобно.

Read more »

Правила для окон в XMonad

Задача - сделать так чтобы все диалоговые окна, некоторые окна по имени класса, некоторые по заголовку и некоторые по ресурсу появлялись плавающими (floating), т.е. не были "тайловыми".

Решение - приводим ~/.xmonad/xmonad.hs в соответствие с нижеследующим примером.

Read more »

Уведомить когда процесс остановиться (shell)

Это маленький, но очень полезный сниплет, который позволяет сэкономить время и сохранить внимание. Я его использую очень часто, когда жду, например, окончания загрузки чего-то куда-то.

10708 - pid процесса который мы ждём:

(while [ $(ps -p 10708 ho pid) ]; do sleep 5; done; echo -e "\a") &

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

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

На основе обсуждения filter tables from database с pgsql-general

Всё очень просто:

SELECT table_name
FROM information_schema.columns
WHERE column_name = 'put_column_name_here';

MVC бэкапы

Перевод MVC Backups с David Fetter's blog

Цель - использовать паттерн Model-View-Controller для создания бэкапов на любой платформе, которую поддерживает PostgreSQL.

Сначала создадим SQL файл, реализующий Model и View:

backup.sql:
WITH t AS ( -- Эта часть Model.
SELECT quote_ident(datname) AS d
FROM pg_database WHERE NOT datistemplate
)
SELECT
'pg_dump -U postgres -Fc --file=' || d || -- Эта часть View.
to_char(now(),'_YYYYMMDD') ||
'.pgbackup' || ' '|| d
FROM t;

Затем реализуем Controller.

psql -Atqf backup.sql | sh

Заметьте, если вы на Windows, то можете подставить cmd.exe или command.com как sh.

Готово!

Как передать NEW из правила в хранимую функцию.

Всё началось на форуме sql.ru - PostgreSQL. Вопрос был следующим:

Как передать NEW из правила в хранимую функцию?

Описание:

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

Интересное генерализирующее решение на основе курсоров сегодня в своём блоге опубликовал автор этого вопроса - Vhubuo. Очень не обычный подход.

А вы бы как это сделали?

Переносим pg_xlog на другой диск под Windows

На днях на форуме sql.ru - PostgreSQL был задан вопрос о переносе pg_xlog под Windows. Т.к. вопрос актуален для России - 1С, Windows сервера, и т.п., решил сделать этот пост.

Собственно особенность прцедуры переноса лишь в одном - символические ссылки.

Read more »

Использование страниц разделяемой памяти

Требуется расширение pg_buffercache.

Запрос помогает понять как используется разделяемая память разными сущностями.

SELECT
c.relname, -- отношение
count(*) AS pages, -- количество страниц
-- использование страниц
sum((usagecount = 0)::int4) as "0",
sum((usagecount = 1)::int4) as "1",
sum((usagecount = 2)::int4) as "2",
sum((usagecount = 3)::int4) as "3",
sum((usagecount = 4)::int4) as "4",
sum((usagecount = 5)::int4) as "5"
FROM
pg_buffercache b
INNER JOIN pg_class c ON
b.relfilenode = c.relfilenode AND
b.reldatabase IN (
0, (SELECT oid FROM pg_database WHERE datname = current_database())
)
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

Проверяем в транзакции мы или нет

Надо проверить была ли транзакция открыта явно, т.е. с помощью BEGIN, и находится ли наш бэкенд в ней, т.е. она ещё до сих пор не закрыта. Решением будет вот такой не большой запрос:

SELECT xact_start < query_start
FROM pg_stat_activity
WHERE procpid = pg_backend_pid();

Блокировки по процессам

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

Удобно смотреть с \x

SELECT
granted, -- удерживает (t)/ожидает (f)
count(1) AS locks, -- ^^^ количество блокировок
pid, -- pid
now() - xact_start AS xact_age, -- длительность транзакции
now() - query_start AS query_age, -- длительность запроса
current_query -- текущий запрос
FROM
pg_locks AS l
LEFT JOIN pg_stat_activity AS a ON
pid = procpid
GROUP BY 1, 3, 4, 5, 6
ORDER BY 1 DESC, 2 DESC
-- ORDER BY 4 DESC
LIMIT 100;

Мониторинг активности

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

Удобно смотреть с \x

SELECT
waiting, -- ждёт?
now() - xact_start AS xact_age, -- длительность транзакции
now() - query_start AS queru_age, -- длительность запроса
procpid, -- pid
current_query -- запрос
FROM
pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY 1 DESC, 2 DESC;

Получаем определения всех индексов указанных типов

Я использовал этот запрос при переводе gist-индексов на gin.

SELECT indexdef
FROM pg_indexes
WHERE indexdef ~ 'USING (gist|gin)';

CHAR(x) или VARCHAR(x) или VARCHAR или TEXT

Выдержка из CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT с select * from depesz;

Оригинальная статья исключительно полно отвечает на один из самых часто задаваемых вопросов о PostgreSQL - Что лучше/быстрее/компактнее CHAR(x) или VARCHAR(x) или VARCHAR или TEXT? Не буду приводить в этом посте полный перевод, а только лишь пару выдержек, т.к., скорее всего, подавляющее большинство читателей заинтересует только ответ без исследования.

Выводы из результатов тестирования:

1. Скорость create table, load data и create index - одинаковый результат для всех типов.


2. Поиск по полям этих типов тоже показал одинаковую скорость.


3. Для типов с ограничением по размеру, если вы не исключаете возможность его изменения в дальнейшем, то имеет смысл использовать TEXT+DOMAIN, т.к. в этом случае будет ShareLock, а не AccessExclusiveLock на таблицу.


4. Если хотите ругаться на превышение размера триггером или CHECK констрейнтом, CHECK даст выигрыш скорости примерно в 2 раза.

Заключение автора статьи (depesz-а):

• char(n) - занимает слишком много места когда имеем дела со значениями короче n, также может приводить к не ожиданным ошибкам из-за дополнения пробелами в конце, плюс проблемы с изменением ограничения размера.


• varchar(n) - проблематично менять ограничение размера на "живой" базе.


• varchar – тоже что и text.


• text – по моему мнению победитель, над (n) типами потому что у него нет их проблем, и над varchar т.к. своё собственное имя.

Мой же выбор - text, и CHECK, если нужно ограничить размер.

Некоторые полезные функции

Выводит NOTICE с указанным текстом в лог. Используется для мониторинга активности, отслеживания прогресса миграции, и т.д., в общем не в процедурах, где вызовы типа RAISE NOTICE не возможны.

CREATE OR REPLACE FUNCTION raise_notice(text)
RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE '%', $1;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

Read more »

Определяем файлы таблицы и всех её индексов

Я столкнулся с этой задачей, когда необходимо было форсировать "поднятие" таблицы и её индексов в файловый кэш утилитой dd, но уверен найдутся и другие способы полезного прменения этого решения.

SELECT oid AS dboid
FROM pg_database
WHERE datname = 'database1';

SELECT relfilenode AS tfile
FROM pg_class
WHERE relname = 'table1';

SELECT i.relfilenode AS ifile, i.relname
FROM
pg_class c
JOIN pg_index x
ON x.indrelid = c.oid
JOIN pg_class i
ON i.oid = x.indexrelid
WHERE c.relname = 'table1';

Путь к файлу таблицы выглядит так:

<path_to_pg_data>/base/<dboid>/<tfile>

А пути к индексам так:

<path_to_pg_data>/base/<dboid>/<ifile>