December 6, 2009

Emacs, файлы отката и авто-сохранения

По умолчанию, для файлов которые вы редактируете, Emacs создаёт файлы отката вида filename~ и файлы авто-сохранения вида #filename#. Первые остаются после закрытия файла и зачастую портят эстетическую составляющую вашего рабочего каталога. В этом посте рассматриваются способы решения этой проблемы.

Если вам совсем не нужен функционал резервного копирования, то вы можете просто отключить его.

~/.emacs.d/general.el:

;; Stop creating backup~ and #auto-save# files
(setq make-backup-files nil)
(setq auto-save-default nil)


Если вы всё же беспокоитесь за безопасность ваших данных, то вы столкнётесь со следующей проблемой. По умолчанию Emacs создаёт резервный файл переименованием оригинального, затем создаёт новый файл с оригинальным именем копированием и открывает его. Таким образом время создания файла меняется, даже если вы не меняли содержимое. Что-бы такого не было можно поменять поведение создание резервного файла на обратное.

~/.emacs.d/general.el:

;; Copy original file when creating backup
(setq backup-by-copying t)


По умолчанию резервные файлы хранятся рядом с их оригиналами, но можно указать директорию, где Emacs должен их создавать. В таком случае если оригинальный файл "/home/gray/tmp/sub/file.txt", а директория для резервного копирования "~/.emacs.d/files-backup", то резервный файл будет именован "~/.emacs.d/files-backup/home!gray!tmp!sub!file.txt".

~/.emacs.d/general.el:

;; Set directory for backup files
(setq backup-directory-alist '(("" . "~/.emacs.d/files-backup")))


В выше приведенном случае, если вы используете длинные имена файлов или глубокие пути, может легко быть достигнут лимит длины имён файлов. Эту проблему можно решить переопределив встроенную функцию определения имени резервного файла make-backup-file-name-function своей, которая будет создавать структуру директорий, такую же как от корня до оригинального файла, в директории для резервного копирования. Для выше приведённого примера резервный файл будет "~/.emacs.d/files-backup/home/gray/tmp/sub/file.txt".

~/.emacs.d/general.el:

;; File path mirroring for backup files
(defun my-backup-file-name (fpath)
(let (backup-root bpath)
(setq backup-root "~/.emacs.d/files-backup")
(setq bpath (concat backup-root fpath "~"))
(make-directory (file-name-directory bpath) bpath)
bpath
)
)
(setq make-backup-file-name-function 'my-backup-file-name)


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

Начальная настройка Emacs

Решил привести в систематизированный вид конфигурацию своего Emacs. В связи с этим решил написать несколько постов с рекомендациями для начинающих "эмаксоидов" о его настройке с нуля, а так как я от выше упомянутых ещё не далеко ушел, буду очень рад комментариям и критике. Этот пост содержит самые базовые вещи, которые надо сделать сразу, что-бы избежать лишних проблем в дальнейшем.

В данный момент я использую Emacs 23.1.1 под Gentoo.

Если вы планируете использовать встроенный инструмент customize совместно с ручной настройкой, то рекомендую выделить для генерируемой им конфигурации отдельный файл.

~/.emacs:

;; Custom settings file
(setq custom-file "~/.emacs.d/custom.el")
(load custom-file)


Также вынесем в отдельный файл общие настройки, оставив для ~/.emacs роль управляющего файла.

~/.emacs:

;; General settings file
(load "~/.emacs.d/general.el")


Заставляем Emacs "общаться" с clipboard-ом X (это не нужно в MS Windows и Mac OS).

~/.emacs.d/general.el:

;; Interact with OS clipboard
(setq x-select-enable-clipboard t)


Убираем Toolbar и Scrollbar. Это конечно по желанию, но по моему они мешают основной идее Emacs.

~/.emacs.d/general.el:

;; Turn off scrollbar, toolbar and menubar
(scroll-bar-mode nil)
(tool-bar-mode nil)
(menu-bar-mode nil)


Включаем отображение номера колонки поинта (курсора).

~/.emacs.d/general.el:

;; Turn on column number mode
(column-number-mode t)


Подсветка парной скобки.

~/.emacs.d/general.el:

;; Highlight brackets
(show-paren-mode t)


Замещаем выделенный текст любым вводом.

~/.emacs.d/general.el:

;; Delete selected text when typing
(delete-selection-mode t)


И устанавливаем шрифт по умолчанию.

~/.emacs.d/general.el:

;; Font
(set-default-font "Monospace-9")


Ну, вот, теперь можно двигаться дальше.

October 16, 2009

Pg_Migrator для 8.4 готов, новое видео

Перевод Pg_Migrator Done for 8.4, New Video с Bruce Momjian Postgres Blog

Последний релиз pg_migrator состоялся более двух месяцев назад. С тех пор я не получил ни одного отчёта об ошибках, более того, было несколько сообщений об успешных миграциях. По этой причине, беру на себя ответственность объявить разработку pg_migrator завершенной и приостановленной до того, как релиз 8.5 будет на подходе.

Также, EnterpriseDB выпустила вводное видео, где я рассказываю о pg_migrator.

July 2, 2009

Итак, вышел PostgreSQL 8.4

«1 июля 2009 — Всемирная группа разработчиков PostgreSQL выпустила новую версию PostgreSQL, продолжая активную разработку самой развитой системы управления базами данных с открытым исходным кодом. Новая версия содержит множество улучшений, делающих администрирование, написание запросов и программирование баз данных PostgreSQL более простым, чем когда-либо. Благодаря 293 новым функциям и улучшениям, содержащимся в версии 8.4, причин выбрать PostgreSQL для вашего нового проекта стало ещё больше.»

Более подробно тут http://postgresmen.ru/articles/view/151

p.s. Я скоро вернусь ;)

March 14, 2009

В ожидании 8.4 - pg_stat_statements

Перевод Waiting for 8.4 - pg_stat_statements с select * from depesz;

4 января Tom Lane применил патч от Takahiro Itagaki, добавляющий новый contrib модуль - pg_stat_statement:

Добавляет contrib/pg_stat_statements для сбора статистики выполнения запросов в рамках всего сервера.

Takahiro Itagaki

Для чего же это? На самом деле это поможет избавиться от некоторых трудностей таким проектам как pgFoouine или мой analyze.pgsql.logs.pl.

В данный момент, если вы хотите увидеть статистику запросов, вам надо логировать их, а затем использовать какое-либо ПО, которое разберёт лог, нормализует запросы и сформирует по ним сводные данные.

Теперь же часть с разбором лога больше не требуется [*].

Вот как это работает.

Во первых, вам потребуется изменить ваш postgresql.conf. Откройте его и найдите параметр shared_preload_libraries. Добавьте туда pg_stat_statements, следующим образом:

shared_preload_libraries = 'pg_stat_statements' # (change requires restart)

Как видно из комментария, изменения требуют перезапуска сервера. Но, перед этим добавим в .conf файл ещё несколько опций:

pg_stat_statements.max = 100
pg_stat_statements.track = top
pg_stat_statements.save = off

Для того чтобы это заработало нам надо добавить "pg_stat_statement" в опцию "custom_variable_classes", которая обычно пустая, но если она у вас уже определена, то просто дополните её вот так:

custom_variable_classes = 'depesz,pg_stat_statements' # list of custom variable class names

Затем можно перезапустить PostgreSQL..

Теперь отслеживание запросов включено, но для просмотра статистики нужно создать соответствующие функции и вью, выполнив pg_stat_statements.sql на любой базе данных:

# \i work/share/postgresql/contrib/pg_stat_statements.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW
GRANT
REVOKE

(конечно же путь моет быть другим).

Что же, посмотрим как это работает. Первым делом проверим (сразу после коннекта) пустую статистику:

# select * from pg_stat_statements;
userid | dbid | query | calls | total_time | rows
--------+------+-------+-------+------------+------
(0 rows)

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

# select * from pg_stat_statements;
userid | dbid | query | calls | total_time | rows
--------+-------+-----------------------------------+-------+------------+------
10 | 16389 | select * from pg_stat_statements; | 1 | 0.000131 | 0
(1 row)

Вау! Работает.

Теперь очистим статистику (select pg_stat_statements_reset();) и выполним несколько тестов:

(pgdba@[local]:5840) 15:42:41 [pgdba]
# select 1 + 2;
?column?
———-
3
(1 row)

(depesz@[local]:5840) 15:40:39 [depesz]
# select 2 + 3;
?column?
———-
5
(1 row)

(depesz@[local]:5840) 15:43:13 [depesz]
# select count(*) from pg_class where relkind = ‘r’;
count
——-
50
(1 row)

Как же теперь выглядит наша статистика?

# select * from pg_stat_statements;
userid | dbid | query | calls | total_time | rows
--------+-------+----------------------------------------------------+-------+------------+------
16384 | 16388 | select count(*) from pg_class where relkind = 'r'; | 1 | 0.000271 | 1
10 | 16389 | select 1 + 2; | 1 | 1.9e-05 | 1
16384 | 16388 | select 2 + 3; | 1 | 2.2e-05 | 1
10 | 16389 | select pg_stat_statements_reset(); | 1 | 3.3e-05 | 1
(4 rows)

Здорово. И как это будет работать с prepared statements?

# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------

(1 row)

# prepare x(int4, int4) as select $1 + $2;
PREPARE
# execute x(1,2);
?column?
----------
3
(1 row)

# execute x(2,3);
?column?
----------
5
(1 row)

(pgdba@[local]:5840) 15:45:54 [pgdba]
# prepare y(int4, int4) as select $1 + $2;
PREPARE

(pgdba@[local]:5840) 15:46:00 [pgdba]
# execute y(3,4);
?column?
———-
7
(1 row)

(pgdba@[local]:5840) 15:46:05 [pgdba]
# select * from pg_stat_statements;
userid | dbid | query | calls | total_time | rows
——–+——-+——————————————+——-+————+——
10 | 16389 | prepare y(int4, int4) as select $1 + $2; | 1 | 1.7e-05 | 1
10 | 16389 | select pg_stat_statements_reset(); | 1 | 3.4e-05 | 1
10 | 16389 | prepare x(int4, int4) as select $1 + $2; | 2 | 3.3e-05 | 2
(3 rows)

Интересно. Смотрится так как будто "prepare" был выполнен столько раз, сколько он был запущен. Не смотря на этот момент - выглядит хорошо.

И так, я настроил pg_stat_statements на хранение 100 различных запросов. Что же случится после сотого? Какой же будет уделён?

Эта простая команда добавит 100 разных запросов:

( echo "SELECT pg_stat_statements_reset();"; for a in $( seq 1 99 ); do echo "select $a;"; done ) | psql

# select count(*) from pg_stat_statements;
count
-------
100
(1 row)

Но "select count(*) from pg_stat_statements" будет также добавлен. Так что, что-то должно быть удалено. Или, может, count(*) не был добавлен к статистике? Давайте проверим:

# select * from pg_stat_statements order by query;
...

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

В заключении, я думаю, что польза от модуля будет намного больше, если он будет сохранять запросы без параметров (т.е. вместо "select 2 + 3" -> "select $1 + $2", как-то так), иначе, на реальных базах данных, буфер запросов будет заполняться слишком быстро, и не будет заметен факт того, что "select * from table where id = 3" и "select * from table where id = 23" практически одно и тоже [*].

Но, по крайней мере уже есть какой-то аналитический инструмент для небольших систем.

От автора перевода:

Вообще странно, по моему автор оригинала что-то путает, в документации по модулю всё выглядит намного лучше - факт того, что "select * from table where id = 3" и "select * from table where id = 23", будет учитываться, т.е. запросы нормализуются. Кроме того, автор почему-то не упомянул о такой важной вещи как "pg_stat_statements.track = all", отслеживании вложенных запросов, например, внутри функций.

UPD.
Я был не прав - Hubert описал всё верно, неточность в незаконченной документации к версии 8.4. Тут наш с ним небольшой диалог, где он представил объяснение и результаты тестов.

March 4, 2009

У блога появился чат-бокс

Теперь, благодаря www.hab.la, вы можете задать мне вопрос прямо на этом блоге и тут же получить от меня ответ. Ищите чат-бокс в правом нижнем углу страницы.

February 11, 2009

Conditional ordering operators

Some times ago I've written a script which creates two operators:

@< - ascending ordering
@> - descending ordering

(here is the script conditional_ordering.sql)

It allows you to replace code like this
if <condition1> then
for
select <fields>
from <tables>
where <restrictions>
order by
field1 desc,
field2
loop
<actions>
end loop;
elsif <condition2> then
for
select <fields>
from <tables>
where <restrictions>
order by
field3,
field1 desc,
field2 desc
loop
<actions>
end loop;
else
for
select <fields>
from <tables>
where <restrictions>
order by
field4
loop
<actions>
end loop;
end if;
that way
for
select <fields>
from <tables>
where <restrictions>
order by
case when <condition1> then
@>field1
@<field2
when <condition2> then
@<field3
@>field1
@>field2
else
@<field4
end
loop
<actions>
end loop;
It looks better, doesn't it?

Also it provides Oracle like OVER PARTITION effect
select * from (
values
(1.2, '2007-11-23 12:00'::timestamp, true),
(1.4, '2007-11-23 12:00'::timestamp, true),
(1.2, '2007-11-23 12:00'::timestamp, false),
(1.4, '2007-01-23 12:00'::timestamp, false),
(3.5, '2007-08-31 13:35'::timestamp, false)
) _
order by
@<column1 ||
case
when column1 = 1.2 then @<column3
when column1 = 1.4 then @>column3
else
@>column2
@<column3
end;

column1 | column2 | column3
---------+---------------------+---------
1.2 | 2007-11-23 12:00:00 | f
1.2 | 2007-11-23 12:00:00 | t
1.4 | 2007-11-23 12:00:00 | t
1.4 | 2007-01-23 12:00:00 | f
3.5 | 2007-08-31 13:35:00 | f
(5 rows)
Note that rows 1-2 and 3-4 have opposite order in third column.

p.s. Unfortunately I haven't manage yet with text fields because of
localization.

Here is the script conditional_ordering.sql

Условное упорядочивание по произвольному набору полей

Вспомнил, вот, свою старенькую наработку и решил опубликовать:

Мне часто приходилось сталкиваться с проблемой дублирования одних и тех же запросов в связи с необходимостью упорядочивания их результатов по разным наборам полей (разные поля, разное количество полей) в зависимости от каких-то условий. В случае с маленьким объёмом кода это решается стандартными средствами, но, когда код выходит за несколько десятков строк, избыточность часто приводит к не хорошим последствиям.

Для решения этой проблемы я написал пару операторов:

@< - сортировка в прямом порядке
@> - сортировка в обратном порядке

(скрипт тут conditional_ordering.sql)

Приведу пример того, как с их помощью можно победить избыточность и придать коду красивый и хорошо читаемый вид. Сначала проблемный код:
if <condition1> then
for
select <fields>
from <tables>
where <restrictions>
order by
field1 desc,
field2
loop
<actions>
end loop;
elsif <condition2> then
for
select <fields>
from <tables>
where <restrictions>
order by
field3,
field1 desc,
field2 desc
loop
<actions>
end loop;
else
for
select <fields>
from <tables>
where <restrictions>
order by
field4
loop
<actions>
end loop;
end if;
Конечно это можно частично обойти за счёт использования курсоров или динамического SQL, но, сами понимаете, в первом случае избыточность в запросах никуда не денется, а во втором появятся проблемы со скоростью. Теперь та же логика, только с использованием новых операторов:
for
select <fields>
from <tables>
where <restrictions>
order by
case when <condition1> then
@>field1
@<field2
when <condition2> then
@<field3
@>field1
@>field2
else
@<field4
end
loop
<actions>
end loop;
Также, как можно заметить из следующего примера, применяя эти операторы можно получить эффект подобный оракловскому OVER PARTITION.
select * from (
values
(1.2, '2007-11-23 12:00'::timestamp, true),
(1.4, '2007-11-23 12:00'::timestamp, true),
(1.2, '2007-11-23 12:00'::timestamp, false),
(1.4, '2007-01-23 12:00'::timestamp, false),
(3.5, '2007-08-31 13:35'::timestamp, false)
) _
order by
@<column1 ||
case
when column1 = 1.2 then @<column3
when column1 = 1.4 then @>column3
else
@>column2
@<column3
end;

column1 | column2 | column3
---------+---------------------+---------
1.2 | 2007-11-23 12:00:00 | f
1.2 | 2007-11-23 12:00:00 | t
1.4 | 2007-11-23 12:00:00 | t
1.4 | 2007-01-23 12:00:00 | f
3.5 | 2007-08-31 13:35:00 | f
(5 rows)
Обратите внимание на то, что строки 1-2 и 3-4 имеют разный порядок в третьей колонке.

p.s. К сожалению операторы пока не работают с текстовыми полями, т.к. мне пока не удалось победить локализацию, да и цели такой пока не было.

Скрипт тут conditional_ordering.sql

February 8, 2009

Как получить элементы enum?

Перевод How to determine what elements are in your enum с Postgres OnLine Journal

Этот вопрос недавно задавали в рассылке для новичков, где Tom Lane дал на него ответ. Если вы используете 8.3 и ENUM, то вам скорее всего это тоже будет интересно. Так что мы посчитали нужным опубликовать его:

Вопрос: Дан ENUM, Есть ли такой запрос, который выведет список всех элементов, допустимых этим ENUM-ом?

Ответ: Да.
CREATE TYPE myenum as enum ('red','green','blue');
SELECT enumlabel
FROM pg_enum
WHERE enumtypid = 'myenum'::regtype
ORDER BY oid;

enumlabel
-----------
red
green
blue
(3 rows)


http://archives.postgresql.org/pgsql-novice/2008-12/msg00043.php
http://www.postgresql.org/docs/8.3/static/catalog-pg-enum.html

Почему делается перепроверка условий (Recheck Cond)?

Перевод Explain: Why do I have to recheck my condition? с Command Prompt Inc., Joshua Drake's blog

Если у вас возник вопрос по PostgreSQL, первым местом, где надо искать ответ должна быть документация по PostgreSQL. Недавно я пересматривал документацию по EXPLAIN, освежая в памяти нюансы по тюнингу запросов и наткнулся на такую вот штуку:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Что сказано в документации по этому поводу:
В данном случае планировщик решил использовать план в два действия: в нижнем узле 
плана посещается индекс с целью поиска местонахождения строк удовлетворяющих
индексному условию, а в верхнем строки извлекаются уже напрямую из таблицы.
Выборочное чтение строк намного более дорогостоящая операция чем их последовательное
чтение, но т.к. не все страницы таблицы требуется посещать оно оказалось выгоднее.
(Причиной является то, что в верхнем узле плана производится сортировка
местонахождений строк в их физическом порядке, что уменьшает цену выборочного чтения.
"bitmap" упомянутый в именах узлов это механизм, который отвечает за
сортировку.)
В кратце, мы проходим по индексу и выясняем какие записи соответствуют индексному условию (unique1 < 100). Единственное что я не понял и не увидел в документации, это то, почему надо делать перепроверку в верхнем узле (Recheck Cond: (unique1 < 100)), если индекс уже сказал мне, какие записи соответствуют условию.

После небольшого расследования и помощи Neil Conway я нашел причину. Bitmap сканирование является не чётким (loosy) и с ростом количества записей, полученных этим сканированием, PostgreSQL перешел из режима "сопоставления записей" в режим "сопоставления страниц". А так как страницы могут содержать несколько записей, необходимо делать перепроверку (Bitmap Heap scan).

February 4, 2009

В ожидании 8.4 - Карты видимости

Перевод Waiting for 8.4 - Visibility maps с select * from depesz;

(от автора) О-да, детка! ;)

Да. Только ради этого патча будет смысл обновиться до 8.4.

Его принял Heikki Linnakangas 3-го декабря. Сообщение:
Добавляет карту видимости. Карта видимости - битовая карта с одним битом на страницу 
данных, где установленный бит указывает, что все записи на странице видимы для всех
транзакций и поэтому страница не нуждается в очистке (vacuuming). Карта хранится в
дополнительном отношении.

Пассивная сборка мусора (lazy vacuum) использует карты видимости для того, чтобы
пропускать страницы, не требующие очистки. Сборка мусора также ответственна за
установку битов видимости. В будущем это даёт возможность реализации index-only
сканирований, но в данный момент нельзя гарантировать, что карты видимости всегда
будут актуальны.

В дополнение к картам видимости теперь доступен флаг PD_ALL_VISIBLE для каждой
страницы данных, который также показывает, что все записи на странице видимы всем
транзакциям. Важно, что этот флаг поддерживается актуальным. Он используется
для пропуска проверок видимости в последовательных чтениях, что даст небольшой
выигрыш при seqscan-ах.
Ранее был ещё один патч, позволяющий autovacuum-у использовать эти возможности.

Даже сейчас 8.4 можно назвать великим. В нём реализовано множество новых возможностей. Кто-то назовёт CTE более важным. Другие скажут, что это локаль на уровне базы.

Но CTE не будет использоваться повсеместно. То же можно сказать о локали уровня базы и практически всех других новых возможностях.

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

Что же мы получили? В кратце - сборка мусора стала быстрее. Возможно намного.

Давайте посмотрим.

Во первых, я запустил версию PG с этим патчем, выключил autovacuum и запустил сборку мусора (vacuum) по всей базе (для того, чтобы избежать неожиданных запусков autovacuum).

Теперь создадим 4 тестовых таблицы:
CREATE TABLE test_1 (i INT4);
CREATE TABLE test_2 (i INT4);
CREATE TABLE test_3 (i INT4);
CREATE TABLE test_4 (i INT4);
добавим данных:
INSERT INTO test_1 SELECT generate_series(1, 100000000);
INSERT INTO test_2 SELECT generate_series(1, 100000000);
INSERT INTO test_3 SELECT generate_series(1, 100000000);
INSERT INTO test_4 SELECT generate_series(1, 100000000);
(знаю, что это не много, но я на ноуте и не хочу ждать всю ночь :)

Теперь обновим таблицы:
UPDATE test_2 SET i = i + 1 WHERE i < 10000000;
UPDATE test_3 SET i = i + 1 WHERE i < 50000000;
UPDATE test_4 SET i = i + 1 WHERE i < 90000000;
Для проверки нового функционала надо запустить vacuum несколько раз, т.к. каждый vacuum использует информацию полученную предыдущим. Т.ч. я сделал:
VACUUM test_1;
VACUUM test_2;
VACUUM test_3;
VACUUM test_4;
после INSERT-ов и после UPDATE-ов. Для большей информативности я ещё раз сделал UPDATE и после него ещё раз vacuum.

Результат:
Visibility maps available Change
No Yes
Vacuum #1
post-insert
test_1 233.34 s 310.53 s +33.1 %
test_2 243.45 s 262.20 s +7.7 %
test_3 237.86 s 260.26 s +9.4 %
test_4 198.72 s 200.60 s +0.9 %
Vacuum #2
post-update #1
test_1 96.71 s 0.38 s -99.6 %
test_2 150.91 s 59.81 s -60.4 %
test_3 283.22 s 234.06 s -17.4 %
test_4 418.41 s 503.25 s +20.3 %
Vacuum #3
post-update #2
test_1 98.11 s 0.54 s -99.4 %
test_2 142.92 s 27.10 s -81.0 %
test_3 283.91 s 297.43 s +4.8 %
test_4 416.35 s 507.77 s +22.0 %

Что получается? На самом деле PostgreSQL с картами видимости отработал медленнее в ситуациях с большим кол-вом "новых" записей, не зависимо от того были ли они добавлены или изменены. Но если ваш autovacuum настроен правильно - этого не случится. Очевидно ему желательно видеть не более ~10% изменений строк, и тогда производительность просто взлетит.

В дополнение к этому, если ваши таблицы (не зависимо от размера) в основном неизменны, то их vacuum будет практически моментален - очень полезное свойство.

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

Хочу заострить внимание на выдержке из сообщения патча:
В будущем это даёт возможность реализации index-only 
сканирований...
Это очень многообещающе. Другие СУБД (включая открытые) реализуют такое через так называемые covering indexes (когда запрос оперирует только полями содержащимися в индексе, и не приходится лезть в таблицу), но PostgreSQL всё ещё удаётся их избегать. Стремления очевидны, и карты видимости - 1-й шаг к окончательной победе :)

February 1, 2009

NULL и NOT IN()

Перевод NULLs vs. NOT IN() с select * from depesz;

Как-то раз мой друг, как ему показалось, нашел ошибку в PG.

Очень простой запрос
select * from table where id not in (select field from other_table)
не возвращал ни одной строки, несмотря на то, что точно существовали такие id, которых не было в other_table.field. Как это?

Для начала смоделируем ситуацию:
CREATE TABLE objects (id INT4 PRIMARY KEY);
CREATE TABLE secondary (object_id INT4);
INSERT INTO objects (id) VALUES (1), (2), (3);
INSERT INTO secondary (object_id) VALUES (NULL), (2), (4);
Данные:
# select * from objects ;
id
----
1
2
3
(3 rows)

# select * from secondary ;
object_id
-----------
[null]
2
4
(3 rows)
Ок. Первым делом проверим:
# SELECT * FROM objects WHERE id IN (SELECT object_id FROM secondary);
id
----
2
(1 row)
Таким образом, опираясь на то, что у нас есть 3-и id объектов (1, 2, 3) и IN работает только для 2-ки, NOT IN должен вернуть 1 и 3. Правильно? Не правильно:
# SELECT * FROM objects WHERE id NOT IN (SELECT object_id FROM secondary);
id
----
(0 rows)

Чё за... Почему?

Проверим результат id NOT IN для каждого id:
# SELECT id, id NOT IN (SELECT object_id FROM secondary) FROM objects;
id | ?column?
----+----------
1 | [null]
2 | f
3 | [null]
(3 rows)
Хм... но почему?

Разберём случай с id = 2.

Сравнение с 2 во второй таблице - id (2) равен 2, т.ч. можем утверждать, что NOT IN вернёт false. Дальнейшая проверка не требуется.
Сравнение с 4 во второй таблице - id (2) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Получается, что для id = 2 у нас будет false, потому что во второй таблице есть строка с object_id = 2.

Но что будет при сравнении с id = 3?

Сравнение с 2 во второй таблице - id (3) не равен 2, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с 4 во второй таблице - id (3) не равен 4, т.ч. пока NOT IN выполняется, но надо проверять далше.
Сравнение с NULL во второй таблице - сравнение id и NULL даст NULL, т.к. NULL рассматривается как "неизвестно". Может равно, а может не равно. И всё ещё требуется дальнейшая проверка.

Для двух строк NOT IN сработало, но для одной - мы не уверены. NULL - "неизвестно", т.ч. оно может оказаться и тройкой (3). Мы не знаем. И по этому NOT IN должен вернуть NULL.

Если посмотреть ближе, то можно увидеть, что технически не возможно получить true в данной ситуации, пока во второй таблице есть значения NULL!

Что делать?

Есть очень простое решение:
# SELECT id, id NOT IN (SELECT object_id FROM secondary WHERE object_id IS NOT NULL) FROM objects;
id | ?column?
----+----------
1 | t
2 | f
3 | t
(3 rows)
Которое возвращает строки с id = 1 и id = 3.

January 31, 2009

Как получить OID таблицы

Перевод How to get a table's OID с BERND'S BLOG

Мои клиенты, пытающиеся работать с OID-ами, часто задают мне вопросы об отношениях, которые надо куда-нибудь добавить (3 раза только на этой неделе). Например, кто-то хочет добавить специальную конфигурацию autovacuum-а для определённой таблицы. Ему надо выяснить OID этой таблицы. Обычно люди используют такой запрос:
SELECT oid FROM pg_class WHERE relname = 'foo' AND relkind = 'r';
oid
-------
16385
(1 row)
Возвращается текущий OID таблицы по её имени, который может в дальнейшем быть использован для ваших нужд. Однако, есть более простой способ получения такой информации, просто приведением имени отношения к типу regclass:
SELECT 'foo'::regclass::oid;
oid
-------
16385
(1 row)
Вернёмся к примеру с autovacuum-ом:
INSERT INTO pg_autovacuum 
VALUES('foo'::regclass, 't', -1, 0.05, -1, -1, -1, -1, -1, -1);
Тут не используется прямое приведение к OID, т.к. будет использовано неявное.

Доступны также другие reg* приведения, например:
SELECT 'now'::regproc::oid;
oid
------
1299
(1 row)

SELECT 'int4'::regtype::oid;
oid
-----
23

В ожидании 8.4 - накапливаем и разворачиваем массивы

Перевод Waiting for 8.4 - array aggregate and array unpacker с select * from depesz;

В нашем распоряжении появилось важное дополнение к PostgreSQL, которое облегчит нам жизнь при работе с массивами.

Теперь решится множество проблем, что обычно решались с помощью трюков, описанных в faq, блог-постах и часто объяснялись в irc.

Первым делом представляю агрегатную функцию для построения массивов. Патч принял Peter Eisentraut, вот с таким сообщением:
агрегатная функция array_agg, как в SQL:2008, но без ORDER BY 

Немного изменена документация с учётом того, что array_agg и
xmlagg имеют схожую семантику и нюансы.

Robert Haas, Jeff Davis, Peter Eisentraut
И так, что это позволяет?

Создадим простую таблицу:
# create table simple_table (client_id int4, order_id int4);
CREATE TABLE
Заполним её случайными данными:
# insert into simple_table (client_id, order_id)
select * from (
select i, j from generate_series(1,4) i, generate_series(1,500) j
) x
where random() < 0.01;
INSERT 0 23
И проверим что получилось:
# select * from simple_table ;
client_id | order_id
-----------+----------
1 | 139
1 | 195
1 | 223
1 | 226
1 | 261
1 | 325
1 | 378
1 | 452
1 | 453
2 | 89
2 | 91
2 | 92
2 | 109
2 | 183
2 | 281
2 | 324
2 | 345
2 | 386
3 | 61
3 | 112
3 | 169
3 | 178
3 | 444
(23 rows)
Круто. Теперь допустим я хочу сделать выборку, перечисляющую все заказы для каждого клиента в одном поле. Раньше мне пришлось бы использовать медленные подзапросы, но сейчас я могу сделать следующее:
# select client_id, array_agg(order_id) from simple_table group by client_id;
client_id | array_agg
-----------+---------------------------------------
2 | {89,91,92,109,183,281,324,345,386}
3 | {61,112,169,178,444}
1 | {139,195,223,226,261,325,378,452,453}
(3 rows)
Конечно результат можно отсортировать, преобразовать в строку или что-то еще:
# select client_id, array_to_string(array_agg(order_id), ', ') || '.'
from (
select client_id, order_id from simple_table order by client_id, order_id
) x group by client_id;
client_id | ?column?
-----------+----------------------------------------------
1 | 139, 195, 223, 226, 261, 325, 378, 452, 453.
2 | 89, 91, 92, 109, 183, 281, 324, 345, 386.
3 | 61, 112, 169, 178, 444.
(3 rows)
Другой патч применил Tom Lane:
Реализует основную форму UNNEST, т.е. unnest(anyarray), 
возвращающую setof anyelement. Тут не хватает опции WITH
ORDINALITY, а так же возможности подавать на вход более
одного массива, что описано в свежей SQL спецификации. Но
это уже вполне полезно, и достаточно для того, чтобы списать
contrib/intagg
Что это делает? Всё просто:
# select * from unnest(array[1,2,3]) i;
i

1
2
3
(3 rows)
Как видно массив просто конвертируется в несколько записей.

Что не мало важно - конверсия рекурсивная:
# select array[array[1,2,3], array[4,5,6], array[7,8,9]];
array
—————————
{{1,2,3},{4,5,6},{7,8,9}}
(1 row)

# select * from unnest(array[array[1,2,3], array[4,5,6], array[7,8,9]]) i;
i

1
2
3
4
5
6
7
8
9
(9 rows)
Создание своей версии unnest довольно тривиально (в случае без рекурсии), но очень здорово то что у нас есть такая встроенная возможность.

В ожидании 8.4 - auto-explain

Перевод Waiting for 8.4 - auto-explain с select * from depesz;

19 ноября Tom Lane применил патч Takahiro Itagaki:
Добавлено расширение auto-explain для автоматического логирования планов медленных запросов
Что оно действительно делает?

Перед тем как я погружусь в детали - небольшое замечание - это первое расширение PostgreSQL (которое я видел) использующее custom_variable_classes GUC.

Конечно же plperl это (GUC) использует, а ещё это используется как временное хранилище между вызовами функций :)

И так, есть 2 способа подключения модуля:
1. LOAD 'auto_explain';
2. shared_preload_libraries = ‘auto_explain’

Первый способ можно использовать в любой сессии (с правами суперюзера), что включит auto-explain только для данной сессии:
# LOAD 'auto_explain';
LOAD
Второй требует правки postgresql.conf, где надо добавить 'auto_explain' в shared_preload_libraries (GUC-переменную).

В этом случае эффект будет для всех сессий.

Так что используем его. Не перепутайте local_preload_libraries и shared_preload_libraries - когда у меня так получилось я не смог запустить PostgreSQL.

После подключения в общем ничего не изменится, всё будет работать как работало до этого, но появится возможность установки доп. переменной:
# set explain.log_min_duration = 5;
что позволит логировать 'explain'
2008-11-23 14:45:14.711 CET depesz@depesz 28352 [local] LOG:  duration: 1003.424 ms  plan:
Result  (cost=0.01..0.03 rows=1 width=0)
InitPlan
->  Result  (cost=0.00..0.01 rows=1 width=0)
2008-11-23 14:45:14.711 CET depesz@depesz 28352 [local] STATEMENT:  select pg_sleep((select 1));
2008-11-23 14:45:14.711 CET depesz@depesz 28352 [local] LOG:  duration: 1005.477 ms  statement: select pg_sleep((select 1));
Последняя строка добавляется из-за ‘log_min_duration_statement’.

Как теперь видно - это очень здорово. Логируется 'explain', но надо учитывать, что если установить нижний порог (explain.log_min_duration) слишком низко, то логи будут расти очень быстро. Планы запросов довольно объёмные.

Дополнительно можно включить логирование "explain analyze", что не очень хорошо, т.к. скажется на производительности, даже если у вас не много запросов выполняющихся дольше explain.log_min_duration.

Причина проста - PostgreSQL вынужден выполнять тайминг всех запросов для того, чтобы иметь возможность вывести результат analyze. Только представьте себе "довесок" от тайминга для сотен запросов в секунду ради пары планов запросов в час.

Ещё одна опция - "explain verbose output" (ознакомиться подробнее можно тут):
# set explain.log_verbose = 1;
SET

# select pg_sleep((select 1));
pg_sleep
----------

(1 row)
Log:
2008-11-23 14:54:48.443 CET depesz@depesz 28812 [local] LOG:  duration: 1001.721 ms  plan:
Result  (cost=0.01..0.03 rows=1 width=0)
Output: pg_sleep(($0)::double precision)
InitPlan
->  Result  (cost=0.00..0.01 rows=1 width=0)
Output: 1
2008-11-23 14:54:48.443 CET depesz@depesz 28812 [local] STATEMENT:  select pg_sleep((select 1));
2008-11-23 14:54:48.443 CET depesz@depesz 28812 [local] LOG:  duration: 1002.139 ms  statement: select pg_sleep((select 1));
Вот, собственно, и всё - хорошее расширение, но будьте с ним осторожны, не забейте весь диск логами...

Замечание от 2010-03-24
Статья была написана до релиза 8.4, с его выходом некоторые вещи могли поменяться, в связи с чем рекомендую также ознакомится с соответствующим разделом документации Appendix F. Additional Supplied Modules - F.2. auto_explain

January 13, 2009

Вот так вот ;)

Диалог с экс-сотрудником:

(18:55:11) gray_hemp: Панча на моё место пересадили или там Дима ещё?
(18:55:30) denis.i: Не щас тут Панч сидит
(18:55:51) denis.i: Кстати я ж твою тачку перемастырил под небольшой тестовый сервачек
(18:56:42) denis.i: Назвали itis.tander а при входе по ssh месага: Сервер имени С.С.Коноплева =)