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 месага: Сервер имени С.С.Коноплева =)