В ожидании 9.0 - дополнение к фреймам window функций

Перевод Waiting for 9.0 – extended frames for window functions с select * from depesz;

(В январе 2010 г. команда разработчиков решила, что следующая версия PostgreSQL будет нумероваться 9.0, а не 8.5)

12 февраля Tom Lane принял патч Hitoshi Harada:

Расширение набора опций фреймов поддерживаемых window функциями.

Патч позволяет фреймам начинаться с текушей строки (CURRENT ROW) (в режиме либо RANGE либо ROW), и также добавляет поддержку ROWS n PRECEDING и ROWS n FOLLOWING для начальной и конечной точек. (PRECEDING/FOLLOWING для RANGE ещё не готово - граматика работает, но это всё что пока есть)

Hitoshi Harada, проверено Pavel Stehule

Read more »

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

Я использовал этот запрос при переводе 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 »

Скрипты psql, занятно и полезно

Перевод Scripting psql for Fun and Profit с David Fetter's blog

Вы можете определённо многое терять не применяя разные полезные возможности. psql принимает большое количество опций коммандной строки. Одна из супер полезных опций это -v, которую можно использовать как эквивалент \set. Например:

psql -1 -v ON_ERROR_STOP=1 -f script.psql

останавливает скрипт на первой ошибке и делает rollback вместо заполнения экрана горой связанных с первой ошибок от дальнейшего содержимого скрипта.

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

Я столкнулся с этой задачей, когда необходимо было форсировать "поднятие" таблицы и её индексов в файловый кэш утилитой 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>

Выполнение произвольных запросов с помощью PL/Proxy

Опишу некоторые мысли по поводу организации шардинга. Это ни в коем случае не претендует на общее правило, но в некоторых ситуациях может оказаться полезным. Перед прочтением необходимо ознакомиться с концепцией PL/Proxy:

https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

Допустим необходимо сделать шардинг на основе PL/Proxy. Также есть приложение, типа ORM генерирующее SQL-запросы, которое тяжело переделать на работу с БД через хранимые функции, что является необходимым для PL/Proxy. Это приложение позволяет с определёнными усилиями в обозримые сроки переработать места инициации запросов, указав как дополнительный параметр критерии шардинга, а структура БД допускает существование таких критериев для каждой выборки.

Read more »

Анализ сборки мусора

Запрс выводит статистическую информацию по сборке мусора упорядочивая таблицы по доле мёртвых кортежей в обратном порядке. Наверху оказываются таблицы у которых ситуация со сборкой мусора хуже всего, но тут необходимо помнить про scale factor и другие настройки, т.к. в "топе" скорее всего появятся ещё и таблицы, которые, например, не требуют сборки мусора из-за малого кол-ва записей или не достаточного увеличения таблицы для запуска autovacuum. Также необходимо помнить и учитывать, что отображаемая статистика это данные, накопленные с момента последней очистки статистики.

Read more »

Яркий пример Культуры написания кода

Один мой хороший товарищ из компании где я раньше работал прислал мне кусок кода экс-сотрудника этой компании, в прямом смысле культурное наследие оставшееся после него:

// Ветки и листья, чекбоксы

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

makeTree = function(treeData) {
// И тут сказал Шекспир: "мне кажется - сие не есть листок бумаги
// белоснежный А4, а папка езмь в которой он лежит"...
var leaf = false;

// ...но вот мы видим, что бездетной матери подобна папка та, печален ее
// рок - исписанной ей быть, как может быть исписан лишь листок...
if (!treeData.child) leaf = true;

// ...и чисел магия не может ум пленить, как возвышают над землей поэтов
// строфы...
// (кстати, а почему бы не treeData.id.toString?)
if (treeData.id == 0) treeData.id = '0';

// ...ВНЕЗАПНО объявил король поход, взять обязал он воинов благородных
// честь и имя (id, name), бумаги пачку A4 (leaf:leaf), в ножнах
// меч (checked:false)...
var node, obj = {id:treeData.id, text:treeData.name, leaf:leaf, checked:false};

// ...и вот стоит отряд на поле как следует в военном деле: там во главе
// король, за ним придворные, бояре, рядовых толпа, холопов массы...
node = new Ext.tree.TreeNode(obj);

// И тут король окликнул "Кто мне верен, из тех, кто близко ко двору?"
if (treeData.child) {
// и собралась вокруг него толпа, что лобызать готова стопы короля...
for (var i = 0; i < treeData.child.length; i++) {
node.appendChild(
// Подобным образом так поступил из них и каждый.
// Так стал отряд подобен видом дереву тем, кто смотрит с высоты
// полета птиц...
makeTree(treeData.child[i])
);
}
}

return node;
}

Вот так вот, высокими категориями человек мыслит! :)

Python for Fun - Транспонируем матрицы

Вспомнил один свой сниплет на Python. Как-то давно проходил собеседование, где получил задачу написать функцию транспонирования матриц. Вот что я им тогда выдал:

gray@gray ~ $ python
Python 2.6.4 (r264:75706, Dec 22 2009, 17:55:44)
[GCC 4.3.4] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> m = [[1, 4, 7], [2, 5, 8], [3, 6, 9]]
>>> map(lambda *l: list(l), *m)
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]
>>> m = [[1, 5, 9], [2, 6, 10], [3, 7, 11], [4, 8, 12]]
>>> map(lambda *l: list(l), *m)
[[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12]]
>>>

Мне сказали, что я сломал всем мозг. Хм... может по этому и на работу не взяли. Но есть ещё одно не менее интересное решение с почти тем же результатом:

>>> m = [[1, 5, 9], [2, 6, 10], [3, 7, 11], [4, 8, 12]]
>>> zip(*m)
[(1, 2, 3, 4), (5, 6, 7, 8), (9, 10, 11, 12)]
>>>