Как получить пустое множество в 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 9.0 Final Release!

Перевод PostgreSQL 9.0 Final Release Available Now! с PostgreSQL

Опубликовано 2010-09-20, press@postgresql.org

Вышел PostgreSQL 9.0! The PostgreSQL Global Development Group анонсирует выход долгожданного релиза. PostgreSQL 9.0 включает встроенную бинарную репликацию и более дюжины других больших нововведений расчитанных на всех от web разработчиков до хакеров БД.

9.0 реализует большее количество крупных возможностей, чем любой релиз до этого, включая:

- Hot standby
- Потоковую репликацию
- In-place обновления
- 64-bit Windows сборки
- Облегченное массовое управления правами
- Анонимные блоки и именованные параметры для хранимых процедур
- Новые windowing функции и упорядоченные агрегаты

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

"Такие нововведения являются прочным основанием тому, что критически важные технические задачи могут продолжать опираться на мощь, гибкость и надёжность PostgreSQL", Afilias CTO Ram Mohan

Больше информации о PostgreSQL 9.0:
- Замечания к релизу
- Пресс-кит
- Руководство по 9.0

Скачать 9.0 сейчас:
- Главная страница загрузки
- Исходный код
- Бинарные пакеты
- Установка в один клик, включая пакеты для Windows

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

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

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

Read more »

В ожидании 9.1 - concat, concat_ws, right, left, reverse

Перевод Waiting for 9.1 - concat, concat_ws, right, left, reverse с select * from depesz;

24 августа Takahiro Itagaki применил патч:

Добавлены строковые функции: concat(), concat_ws(), left(), right()
и reverse().

Pavel Stehule, проверено мной.

Что это за функции?

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") &

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

В ожидании 9.1 - Распознавание функциональной зависимости от первичных ключей

Перевод Waiting for 9.1 – Recognize functional dependency on primary keys с select * from depesz;

Вчера (7 августа) Tom Lane применил:

Распознавание функциональной зависимости от первичных ключей. Это позволяет
колонкам не присутствовать в GROUP BY, если там присутствует первичный ключ.

В дальнейшем нам стоит также разрешить функциональную зависимость от UNIQUE
ограничений при условии, что колонка помечена как NOT NULL, но это будет ждать пока
NOT NULL ограничения не будут представлены в pg_constraint, т.к. нам будут нужны
pg_constraint OID-ы для всех условий, где будет разрешаться функциональная
зависимость.

Peter Eisentraut, проверено Alex Hunsaker и Tom Lane

Одна из наиболее частых проблем, с которой люди сталкиваются при переходе с MySQL на PostgreSQL, заключается вот в таких запросах:

SELECT field_a, field_b, count(*)
FROM TABLE
GROUP BY field_a

Это нормально для MySQL, но не работало в PostgreSQL.

Read more »

В ожидании 9.1 - Снижаем уровни блокировок для ALTER TABLE

Перевод Waiting for 9.1 – Reduced lock levels for ALTER TABLE с select * from depesz;

28 июля Simon Riggs применил патч:

Снижает уровни блокировок CREATE TRIGGER и некоторых действий ALTER TABLE,
CREATE RULE. Убирает прописанные на прямую в коде режимы блокировок, используемые во
множестве команд изменения DDL, позволяя более легко менять уровни блокировок в
будущем. Реализован начальный анализ DDL подкомманд, так что многие уровни блокировок
теперь будут ShareUpdateExclusiveLock или ShareRowExclusiveLock, позволяя конкретным
коммандам не блокировать чтение/запись. Это первое изменение из числа запланированных
в этом направлении; будет нужна дополнительная документация когда весь проект
завершится.

Во первых - это только начало. Конечная цель - сделать все (большинство?) выражения ALTER TABLE менее навязчивыми.

Read more »

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

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

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

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

В ожидании 9.1 - CREATE TABLE IF NOT EXISTS

Перевод Waiting for 9.1 – CREATE TABLE IF NOT EXISTS с select * from depesz;

25 июля Robert Haas применил патч, добавляющий CREATE IF NOT EXISTS для таблиц.

CREATE TABLE IF NOT EXISTS.

Reviewed by Bernd Helmle.

Пример тривиален:

$ create table if not exists tesit (x text);
CREATE TABLE

$ create table if not exists tesit (x text);
NOTICE: relation "tesit" already exists, skipping
CREATE TABLE

Как можно видеть ошибки нет - просто дружелюбное замечание.

Скорее всего такое же будет для схем, индексов, вью и других объектов базы, и нам не потребуется больше все эти корявые воркэраунды.

Как можно задать порядок результата

Перевод How to order by some random - query defined - values? с select * from depesz;

Представим простую ситуацию - есть таблица каких-то объектов (каждый со своим id) откуда надо достать объекты с id 3, 71, 5 и 16. И, самое главное, в том же порядке!

Как это сделать?

Read more »

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.

Готово!

В ожидании 9.1 - \conninfo в psql

Перевод Waiting for 9.1 – \conninfo in psql с select * from depesz;


20 июля Robert Haas применил патч, добавляющий ещё одну \* комманду в psql:

Добавляет команду \conninfo в psql, показывающую информацию о текущем соединении.

David Christensen. Проверено Steve Singer. Некоторые изменения от меня.

Из сообщения всё предельно понятно, т.ч. просто посмотрим на это:

Read more »

В ожидании 9.1 - standard_conforming_strings = on

Перевод Waiting for 9.1 – standard_conforming_strings = on с select * from depesz;

В основном я пишу о новых возможностях, но это изменение довольно таки важное.

20 июля Robert Haas сделал следующие изменения:

Значение standard_conforming_strings по умолчанию теперь on.

Это изменение должно быть доведено до сведения разработчиков драйверов и в
замечаниях к релизу должно быть указанным как не совместимое с предыдущими
релизами.

Что это и почему так важно?
Допустим вы хотите выбрать какое-то значение содержащее символ ' (апостроф). Т.к. строки тоже определяются этим символом нам нужно замаскировать его.

Долгое время можно было делать так:

$ SELECT 'guns \'n roses';
?COLUMN?
---------------
guns 'n roses
(1 row)

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

Read more »

Скрываем dot-файлы в Dired (Emacs)

Если пользуетесь Dired, то наверняка часто сталкивались с проблемой, когда, допустим, в вашем "хоуме" (~/) глаза разбегались от обилия dot-файлов (.filename). Действительно так очень неудобно, но есть решение - просто добавьте этот сниплет в конфигурацию:

~/.emacs.d/general.el:
;; Dired Mode extra features
(load "dired-x.el")
(setq dired-omit-files
(concat dired-omit-files "\\|^\\..+$"))
(dired-omit-mode 1)

По умолчанию он выключает отображение dot-файлов. Чтобы переключать режим отображения используйте M-o.

Установка Londiste в подробностях

Этот пост содержит подробное пошаговое описание процесса настройки репликации на основе Londiste, системы асинхронной мастер-слэйв репликации из пакета SkyTools от Skype.

Допустим есть 2 сервера - host1 и host2. На host1 работает кластер с одной или несколькими базами, которые необходимо реплицировать на host2. Другими словами host1 будет мастером, а host2 слейвом.

Прежде всего необходимо установить пакет SkyTools. Его исходники можно найти на официальном сайте проекта или в wiki. Там же найдёте всю документацию и ссылки на дополнительные материалы. Советую обращаться к ним если захотите узнать дополнительные подробности о вещах, которых я буду в данной статье касаться поверхностно.

Итак, всё ПО необходимое для репликации установлено, но, прежде чем начать настройку, хочу рассказать о принципах работы Londiste в очень общих словах.

Read more »

Перенимаем NoSQL

Перевод Learning from NoSQL с Bruce Momjian: Postgres Blog

Я думаю, что первый урок, который PostgreSQL сообщество может вынести из NoSQL, в том, что не всем нужны все наши возможности, и, если это не будет сложно, нам необходимо дать пользователям возможность отказаться от некоторых из них, в пользу желаемых NoSQL преимуществ. В некоторых случаях мы уже можем это сделать:

- Вы можете улучшить скорость за счёт откладывания или отказа от надежности (synchronous_commit, fsync)
- Вы можете убрать затраты на целостность не устанавливая ограничения (constraints)
- Можете использовать подготовленные (prepared) запросы для устранения затрат на парсера и оптимизатора
- Массивы часто могут быть использованы для ухода от затрат на join
- Можно хранить не структурированные данные в hstore
- Устаревшие данные можно обслуживать с помощью асинхронной мульти-мастер репликации (Bucardo)

Однако есть некоторые вещи, которые будет сложно осуществить:

- Доступ к данным без SQL
- Снижение затрат за счёт отказа от атомарности и изоляции

Я думаю о новых опциональных возможностях, которые мы могли бы предоставлять потенциальным NoSQL пользователям, но тут надо всё хорошо продумывать.

Дополнение: Один из пунктов в нашем TODO-листе, который может помочь потенциальным NoSQL пользователям, это реализация встроенного типа данных JSON. JSON используется в качестве формата хранилища во многих NoSQL базах.

Несколько слов о NoSQL

Перевод Settling for NoSQL с Bruce Momjian: Postgres Blog

В последнее время вокруг NoSQL баз данных довольно много шума, и, багодаря посещению конференции Emerging Technologies for the Enterprise Conference, я узнал о них больше. Я прослушал рассказ о Cassandra, и ещё один о MongoDB.

NoSQL базы имеют несколько отличий от реляционных:

- "голое" обращение к данным, другими словами отсутствие языка запросов (клиент делает большинство того, что обычно делается с помощью SQL)
- отсутствие join-ов (данные должны соединяться на стороне клиента)
- жертвуется ACID и транзакционные свойства ради скорости, отказоустойчивости, простоты добавления/выведения нод

Кого-то может смутить отсутствие этих значительных возможностей реляционных баз, но, если нужный вам отклик и требования к инфраструктуре не вписываются в рамки реляционных баз, и вы можете принять такие ограничения (думаю социальные медиа или поисковые движки), тогда NoSQL вполне имеет смысл.

Как передать 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;

PostgreSQL 9.0: Добавлена эмуляция MySQL (MySQL Emulation Layer)

Перевод PostgreSQL 9.0: Includes the new MySQL Emulation Layer с ads' corner

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

Сегодня в PostgreSQL 9.0 была добавлена возможность эмуляции MySQL, так называемый MySQL Emulation Layer.

Для активации этой возможности просто включите CUG опцию mysql_compatible в "on".

postgres=# SELECT * FROM pg_settings WHERE name = 'mysql_compatible';
-[ RECORD 1 ]----------------------------------------------------------------
name | mysql_compatible
setting | ON
unit |
category | Version AND Platform Compatibility / Other Platforms AND Clients
short_desc | Enable MySQL Emulation Layer
extra_desc |
context | backend
vartype | bool
source | DEFAULT
min_val |
max_val |

Read more »

Панель CPU conky + dzen2 + xmonad

Итак, в след за постом о панели питания публикую мою панель состояния CPU. Напомню что речь идёт о dzen2 панели для Xmonad генерируемой с помощью conky.

Что я хотел получить (постановка задачи):

- график общей активности процессора во времени;
- индикатор текущей активности для каждого ядра;
- текущую частоту для каждого ядра;
- общий процент текущего использования;
- общую температуру.

Как это выглядит в итоге:

Read more »

Панель питания conky + dzen2 + xmonad

На выходных продолжил копать conky, dzen2 и xmonad и сделал пару панелек для верхнего бара. Сегодня расскажу про панель питания.

Задача была следующая:

- информировать от сети или от батареи сейчас питание;
- процент зарядки батареи с визуализальным индикатором;
- оставшееся время работы батареи, если работает от питания.

Вот так это выглядит:

Read more »

Опять про отступы в sql-mode Emacs

Сделал не большую правку в конфиге для sql-mode. Теперь когда просто нажимаешь "TAB" всегда делается отступ в 4 пробела, а когда нажимаешь "C-j" делается переход на новую строку с относительным отступом там где надо.

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

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

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

Postgres участвует в Google Summer of Code 2010

Перевод Postgres participating in Google Summer of Code 2010 с PostgreSQL: News

Пост от 2010-03-20
Автор xzilla@users.sourceforge.net

Я счастлив сообщить что Postgres был выбран для участия в программе Google Summer of Code этого года. В следующую пару недель мы будем утверждать наставников; если вы работаете над Postgres и хотели бы взять под своё начало ученика, сообщите мне об этом, чтобы мы могли вас записать. Если вы ученик и хотели бы работать над Postgres, присылайте свои предложения. Набор учеников начнётся 29 марта, т.ч. мы бы хотели чтобы наставники были готовы, и чтобы студенты обсудили с разработчиками Postgres свои предложения. Если у кого-то есть вопросы, пишите мне на email или найдите меня в irc.

Полезные ссылки для Postgres GSoC:

Our ideas page for GSoC

Our loose attempt at organization

Our Postgres page on the GSoC site

Users Guide to GSoC

Спасибо всем, буду ждать ещё одного интересного года c GSoC и надеюсь что вам понравится.

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

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

Удобно смотреть с \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;

В ожидании 9.0 (8.5) - Информация об использовании буферов в EXPLAIN

Перевод Waiting for 8.5 – buffers info for explain с select * from depesz;

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

Сегодня речь пойдёт о маленьком, но (по крайней мере для меня) действительно полезном патче. Принял его 15 декабря Robert Haas, а сделал Itagaki Takahiro:

Добавляет опцию EXPLAIN (BUFFERS) для отображения статистики
использования буферов.

Патч также убирает эту статистику из вывода track_counts, т.к. EXPLAIN
(или глобальная статистика) теперь считается лучшим местом для такой
информации.

Itagaki Takahiro, проверено Euler Taveira de Oliveira.

Read more »

В ожидании 9.0 (8.5) - Агрегаты с упорядочиванием

Перевод Waiting for 8.5 – ordered aggregates с select * from depesz;

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

15 декабря Tom Lane применил патч от Andrew Gierth (aka RhodiumToad), добавляющий интересную возможность:

Поддержка ORDER BY в агрегатных функциях, наконец-то не хак-решение того в каком порядке значения будут агрегироваться. На ряду с этим снимается ограничение использования DISTINCT в агрегатах с одним и только одним аргументом.

Возможно стоит упомянуть о изменении в поведении: ранее agg(DISTINCT x) всегда выкидывала null-значения. Теперь это происходит только тогда, когда transition-функция агрегата реализует это ограничение. Иначе null-значения обрабатываются так, как обычно делает DISTINCT, т.е. возвращается одна копия.

Andrew Gierth, проверено Hitoshi Harada

Read more »

Замечания к релизу 9.0

Перевод 9.0 Release Notes Done с Bruce Momjian's blog

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

Суббота, 20 Марта 2010

Я закончил замечания к релизу 9.0, тут вы можете найти их онлайн. Конечно же к финальной версии там ещё будет много правок.

PL/pgSQL по умолчанию

Перевод Waiting for (9.0) 8.5 – PL/pgSQL by default с select * from depesz;

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

18 декабря Bruce Momjian прменил очень важный, но относительно не большой патч:

Устанавливает язык PL/pgSQL по умолчанию.

Нет смысла показывать пример, т.к. сообщение говорит обо всём - начиная с новой версии язык PL/pgSQL будет доступен по умолчанию во всех базах.

Было время, когда пользователи отказывались от хранимых процедур в связи с тем, что у них не было ни какого PL/ языка, а его установка требовала прав суперюзера. Начиная с 8.3 уже не надо было быть суперюзером, чтобы установить доверительний (trusted) язык, т.о. проблема почти исчезла, но сейчас она просто переста существовать :)

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

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

Удобно смотреть с \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;

В ожидании 9.0 (8.5) - Изменения в VACUUM FULL

Перевод Waiting for 8.5 – VACUUM FULL change с select * from depesz;

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

Некоторое время назад Josh Berkus написал о возможных изменениях в VACUUM FULL. Теперь это воплотилось в жизнь. Под "теперь" я имею ввиду 6 января, когда Takahiro Itagaki применил свой патч:

Поддержка основанного на перезаписи полного вакуума как
VACUUM FULL. Традиционный VACUUM FULL был переименован в VACUUM
FULL INPLACE. Также добавлена опция -i, --inplace в vacuumdb для
FULL INPLACE ваккума.

Т.к. новый VACUUM FULL использует инфраструктуру от CLUSTER, мы
не можем использовать его для системных таблиц. VACUUM FULL для
них прозрачно преобразуется в VACUUM FULL INPLACE.

Itagaki Takahiro, проверено Jeff Davis и Simon Riggs.

Read more »

В ожидании 9.0 - Потоковая репликация

Перевод Waiting for 9.0 – Streaming replication с select * from depesz;

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

Это очень важный функционал, возможность благодаря которой PostgreSQL сделал скачёк от 8.4 до 9.0. Патч разработан Fujii Masao и принят Heikki Linnakangas 15 января 2010.

Добавляет Потоковую репликацию (Streaming replication).

Включает два новых вида процессов postmaster-а - walsender и walreceiver. Walreceiver отвечает за соединение с главным сервером и передачу WALL данных на диск, в то время как walsender работает на главном сервере и передаёт клиенту WAL данные с диска.

Документация всё ещё нуждается в доработке, но основа уже есть. Возможно мы позже вынесем секцию касающуюся репликации в отдельную главу, также как и секцию о репликации на основе файлов. Это будет в отдельном патче, чтобы было понятно что добавлено/изменено. Этот патч также добавляет новую секцию в главу о FE/BE протоколе, касающуюся walsender/walreceivxer

Подняли версию каталога из-за двух новых функций pg_last_xlog_receive_location() и pg_last_xlog_replay_location() для мониторинга прогресса репликации.

Fujii Masao, с дополнительными правками от меня

Read more »

Небольшое предпочтение в sql-mode Emacs

В общем чуток кастомизации sql-mode. Я привык в SQL к отступам в 4-е пробела и мне нравится контроль над пробельными символами (мой пунктик).

В управляющем файле ставим загрузку конфигурации для разных sql:

~/.emacs:
(load "~/.emacs.d/sql.el")

В конфигурации sql:

~/.emacs.d/sql.el:
;; Minor modes
(add-hook 'sql-mode-hook 'whitespace-mode)

;; 4 spaces instead of tab
(add-hook 'sql-mode-hook
'(lambda ()
(setq indent-tabs-mode nil
tab-width 4
indent-line-function 'insert-tab)
(define-key sql-mode-map (kbd "C-j")
'(lambda()
(interactive)
(delete-horizontal-space t)
(newline)
(indent-relative-maybe)))))

Всё, теперь меня ничего не смущает, гут :)

Правка от 2010-03-26
Установил функцию общего отступа в обычную табуляцию:

indent-line-function 'insert-tab

И переопределил "C-j" с использованием относительного отступа где надо:

(define-key sql-mode-map (kbd "C-j")
'(lambda()
(interactive)
(delete-horizontal-space t)
(newline)
(indent-relative-maybe)))))

В ожидании 9.0 - размеры таблиц и индексов

Перевод Waiting for 9.0 – table and index sizes с select * from depesz;

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

19 января Tom Lane применил замечательный патч:

Добавлены pg_table_size() и pg_indexes_size() более удобные надстройки над функцией pg_relation_size().

Bernd Helmle, проверил Greg Smith

Read more »

Настраиваем Emacs для работы с PostgreSQL

Да-да, в Emacs всё уже давно есть. Просто делаем M-x sql-postgres, он спрашивает базу+пользователя+хост, вводим пароль и получаем буфер-терминал *SQL*, где видим всем известный инструмент psql для работы с PostgreSQL.

Но, если получаем длинный вывод, то наблюдаем артефакты от less или more (или что у вас там по умолчанию в качестве пейджера). К тому же, если работаем напрямую из окна в sql-mode (что очень удобно, см. M-x describe-function RET sql-set-sqli-buffer RET), то наблюдаем эффект с множественными повторениями подсказки (prompt).

Как это пофиксить? Идея в том, чтобы отключить PAGER (программу, через которую psql пропускает свой вывод), и "подтюнить" подсказку (prompt) средствами самого psql с помощью переменной sql-postgres-options, предоставляемой sql-mode.

Создаём файл с настройками для PostgreSQL.

~/.emacs.d/postgresql.el:
;; In psql turn off pager usage and adjust prompts
(setq sql-postgres-options '("-P" "pager=off"
"-v" "PROMPT1=%n@%m:%> %~%R%#\n"
"-v" "PROMPT2="
"-v" "PROMPT3="))


Далее добавляем подгрузку этих настроек в .emacs и всё.

~/.emacs:
(load "~/.emacs.d/postgresql.el")

В ожидании 9.0 - string_agg (агрегируем строки)

Перевод Waiting for 9.0 – string_agg с select * from depesz;

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

1 февраля Takahiro Itagaki применил патч от Pavel Stehule, который добавляет агрегат string_agg:

Добавляет агрегатные функции string_agg. Версия с одним аргументом конкатенирует входные значения в строку, с двумя аргументами то же самое, но ещё добавляет разделитель между элементами.

Патч от Pavel Stehule, проверен David E. Wheeler и мной.

Read more »

В ожидании 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)]
>>>

Не оптимальная запись

Если необходимо локализовать проблемы связанные с не оптимальной записью, а это очень важные проблемы, т.к. предел возможностей БД в основном упирается в дисковые операции, и начать решать их с более тяжёлой, то этот запрос поможет вам. Он выводит статистику по таблицам в обратном порядке по сумме операций записи. Сверху будут таблицы с наиболее интенсивной записью. В условиях осуществляется фильтрация по схемам, чьи таблицы необходимо исключить из статистики.

Т.о. выявляются счётчики, лишние или временные вставки и т.п. Далее, интенсивность записи счётчиков можно снизить за счёт, например, накопления их в памяти (допустим в memcached) по 10 штук и одного сброса на диск. Где-то можно просто отказаться от некоторых операций, а где-то выявится необходимость рефакторинга приложения.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
seq_scan + idx_scan AS re, -- всего чтений
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
WHERE schemaname NOT IN ('pgq')
ORDER BY
n_tup_upd + n_tup_ins + n_tup_del DESC
LIMIT 40;

Не оптимальное чтение

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

Также удобно бывает использовать ограничение по размеру (в примере ниже оно закомментировано). Оно помогает отфильтровывать таблицы, по которым последовательные чтения потенциально выгодны. Тут необходимо смотреть на величину, и вообще на факт, необходимости использования по ситуации, проведя несколько экспериментов.

SELECT
schemaname AS sch, -- схема
relname AS tab, -- таблица
pg_size_pretty(pg_relation_size(relid)) AS tsize, -- размер
seq_scan AS ss, -- последовательных чтений
idx_scan AS is, -- индексных чтений
seq_scan + idx_scan AS re, -- всего чтений
n_tup_upd + n_tup_ins + n_tup_del AS wr, -- операций записи
n_tup_ins AS i, n_tup_upd AS u, n_tup_del AS d -- I/U/D
FROM
pg_stat_user_tables
-- WHERE pg_relation_size(relid) > 1 * 1024 * 1024 -- ограничение по размеру
ORDER BY
length(seq_scan::text) DESC,
pg_relation_size(relid) DESC
LIMIT 40;

Не используемые индексы

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

Запрос действует только по пользовательским индексам и не принимает во внимание уникальные, т.к. они используются как ограничения (как часть логики хранения данных). Тем не менее прежде чем принять решение об удалении хорошо подумайте.

SELECT
idstat.schemaname AS sch, -- схема
idstat.relname AS tab, -- таблица
indexrelname AS idx, -- индекс
idstat.idx_scan AS iis, -- число сканирований по этому индексу
pg_size_pretty(pg_relation_size(indexrelid)) AS isize, -- размер индекса
tabstat.idx_scan AS tis, -- индексных чтений по таблице
tabstat.seq_scan AS tss, -- последовательных чтений по таблице
tabstat.seq_scan + tabstat.idx_scan AS tre, -- чтений по таблице
n_tup_upd + n_tup_ins + n_tup_del AS twr, -- операций записи
pg_size_pretty(pg_relation_size(idstat.relid)) AS tsize -- размер таблицы
FROM
pg_stat_user_indexes AS idstat
JOIN pg_indexes ON
indexrelname = indexname AND
idstat.schemaname = pg_indexes.schemaname
JOIN pg_stat_user_tables AS tabstat ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
ORDER BY
idstat.idx_scan,
pg_relation_size(indexrelid) DESC
LIMIT 20;

Индикатор раскладки клавиатуры xxkb в Xmonad

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

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

Далее добавляем в ~/.xmonad/xmonad.hs его запуск при загрузке:

main = do
...
spawn myXxkbBar
...

myXxkbBar = "xxkb" -- configuration in ~/.xxkbrc

Да, не забываем подрегулировать свои панели (dzen, xmobar, и т.д.) чтобы в правом верхнем углу был свободный квадрат 15x15 пикселей, где и разместится индикатор. Затем указываем, что его необходимо снимать при перезапуске:

-- Do not leave useless conky, dzen and xxkb after restart
((modm, xK_q), spawn "killall conky dzen2 xxkb; xmonad --recompile; xmonad --restart"),

И игнорировать в ManageHook:

myManageHook = composeAll [
resource =? "XXkb" --> doIgnore
]

Почти всё готово, осталось прописать конфигурацию xxkb в ~/.xxkbrc (измените 1265 на ваше разрешение по y минус 15):

XXkb.group.base: 1
XXkb.group.alt: 2
XXkb.mainwindow.appicon: yes
XXkb.mainwindow.geometry: 15x15+1265+0
XXkb.mainwindow.xpm.1: en15.xpm
XXkb.mainwindow.xpm.2: ru15.xpm
XXkb.button.enable: no
XXkb.controls.add_when_start: no
XXkb.controls.add_when_change: yes
XXkb.controls.focusout: yes
XXkb.controls.mainwindow_delete: no

И, вуаля, получаем то, что требовалось:



Подробнее про xxkb на русском можно почитать тут Индикатор-переключатель раскладки клавиатуры XXKB.

Мою последнюю конфигурацию Xmonad и всё её сопровождающее найдёте здесь Xmonad/Config_archive (поищите gray_hemp).

Важность очистки статистики

Это маленький но очень важный момент. Если ваш проект интенсивно развивается, то накопленная за долгий период времени внутренняя статистика PostgreSQL зачастую теряет смысл. Так, например, добавление нового индекса или изменение пары запросов меняет темпы накопления и новая ситуация начинает накладываться на старую, из-за чего можно сделать не правильные выводы. Для того чтобы избежать этого неообходимо очищать старые статистические данные перед запросом статистики, если с момента предыдущей очистки были какие-то изменения влияющие на её сбор.

В PostgreSQL для этого существует специальная функция:

SELECT pg_stat_reset();

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

Конфигурация Xmonad

Мои настройки Xmonad (в первом приближении) опубликовали в Xmonad/Config archive, смотрите /gray_hemp's xmonad.hs и /gray_hemp's .conky_timegray_hemp.

Из особенностей: dzen + conky с иcпользованием иконок layout-ов, resizable tall, xmonad shell prompt, urgency popup плюс форматирование status bar-а.