gray-hemp: PostgreSQL, Emacs, Linux, etc.

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

2010-11-01T09:03:00.005+03:00

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

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

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

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

field IN (???)

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

postgres@localhost:5432 test=#
SELECT 1 IN ();
ERROR:  syntax error at or near ")"
LINE 1: SELECT 1 IN ();
                     ^

Просто NULL тоже нельзя.

postgres@localhost:5432 test=#
SELECT 1 IN NULL;
ERROR:  syntax error at or near "NULL"
LINE 1: SELECT 1 IN NULL;
                    ^

NULL в скобках опять нельзя, т.к. в результате всегда будет NULL.

postgres@localhost:5432 test=#
SELECT 1 IN (NULL);
 ?column? 
----------
 
(1 row)

Как тогда быть? Ответ получается из вопроса - просто получить пустое множество.

postgres@localhost:5432 test=#
SELECT 1 IN (SELECT 1 WHERE FALSE);
 ?column? 
----------
 f
(1 row)

postgres@localhost:5432 test=#
SELECT 1 NOT IN (SELECT 1 WHERE FALSE);
 ?column? 
----------
 t
(1 row)

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

2010-10-29T08:44:00.003+04:00

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

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

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

Зачем CASCADE в DROP INDEX

2010-10-27T09:37:00.005+04:00

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

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

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

2010-10-27T09:19:00.003+04:00

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

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

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

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

2010-10-27T08:54:00.006+04:00

Вчера на 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!

2010-09-20T16:08:00.004+04:00

Перевод 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 закрытом на сервере

2010-09-07T18:45:00.007+04:00

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

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

Всё очень просто - нам поможет старый добрый ssh-туннелинг. Допустим есть сервер servername где на локальном порту 5432 крутится PostgreSQL, и есть пользователь username, который может ходить по ssh на этот сервер. Надо сделать так чтобы обращаясь к локальному порту 5432 на вашей рабочей машине вы фактически попадали на локальный порт 5432 нашего сервера.

Если вы на Windows, то смотрите в сторону утилиты PuTTY, я в своё время на ней такое делал. Если вы на Linux, как и я сейчас, то вам нужна такая комманда:

ssh -L 5432:localhost:5432 username@servername

У меня, кстати, несколько серверов пробрасываются на разные локальные порты на рабочей машине:

ssh -L 5432:localhost:5432 gray@server1
ssh -L 6432:localhost:5432 gray@server2
ssh -L 7432:localhost:5432 gray@server3

Очень удобно.

p.s. Ещё одна полезная ссылка, где рассказывается как пользоваться ssh по сертификату, т.е. без надобности вводить каждый раз пароль.

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

2010-09-07T15:48:00.004+04:00

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

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

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

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

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

concat() это просто функциональный аналог оператора || с возможностью обработки нескольких строк за раз:

$ SELECT concat( 'post', 'gres', 'ql' );
   concat   
------------
 postgresql
(1 row)

Очень просто и полезно если надо сделать много конкатенаций.

concat_ws() похожа, но воспринимает первый аргумент как разделитель:

$ SELECT concat_ws( ' ', 'hubert', 'depesz', 'lubaczewski' );
         concat_ws         
---------------------------
 hubert depesz lubaczewski
(1 row)

left() и right() делают одно и то же, но с разных сторон строки:

$ SELECT LEFT( 'postgresql', 4 );
 LEFT 
------
 post
(1 row)

В общем это работает как substring(), но тут есть одна приятная возможность:

$ SELECT LEFT( 'postgresql', -2 );
   LEFT   
----------
 postgres
(1 row)

Это значит, что если параметр длинны отрицательный, то функция будет обрезать abs(length) символов с "другой стороны".

Примеры для right():

$ SELECT RIGHT( 'postgresql', 3 );
 RIGHT 
-------
 sql
(1 row)
 
$ SELECT RIGHT( 'postgresql', -4 );
 RIGHT  
--------
 gresql
(1 row)

Это всё относительно просто можно было сделать раньше. Но последняя функция, по моему не такому уж скромному мнению, является наиболее важной - reverse(). Её задача тривиальна:

$ SELECT reverse( 'postgresql' );
  reverse   
------------
 lqsergtsop
(1 row)

Но то, что она в ядре PostgreSQL означает что мы теперь можем сделать быстрый (индексный поиск) с условием вида LIKE '%something' ('%' в начале!). Техника для этого была описана ранее, но тогда небыло быстрой reverse().

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

2010-09-07T15:26:00.000+04:00

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

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

import XMonad.Hooks.ManageDocks
import XMonad.Hooks.ManageHelpers
...
main = do
  ...
  xmonad $ myUrgencyHook $ defaultConfig {
    ...
    manageHook = myManageHook <+> manageDocks <+> manageHook defaultConfig,
    ...
    }
...
-- Window rules
myManageHook = composeAll . concat $ [
  [isDialog --> doFloat],
  [className =? c --> doFloat | c <- myCFloats],
  [title =? t --> doFloat | t <- myTFloats],
  [resource =? r --> doFloat | r <- myRFloats],
  [resource =? i --> doIgnore | i <- myIgnores]
  ]
  where
    myCFloats = ["Xmessage"]
    myTFloats = ["Save As...", "Save File"]
    myRFloats = []
    myIgnores = ["XXkb"]

В переменную myCFloats прописываем имена всех классов окон, которые нужно отображать плавающими, соответственно в myTFloats заголовки и в myRFloats ресурсы.

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

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

2010-08-18T10:29:00.002+04:00

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

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

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

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

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

2010-08-11T20:45:00.010+04:00

Перевод 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.

Теперь, с новым патчем, это подмножество запросов будет работать и в PostgreSQL. Во первых, что это за подмножество? Всё просто - если field_a первичный ключ данной таблицы и мы группируем по нему, то очевидно, что мы не получим более чем одной строки и значения field_b для каждого из значений field_a. Но если это не первичный ключ, то может получиться что для каких-то значений field_a будет несколько значений field_b, и в этом случае запрос уже не подходит.

Рассмотрим пример:

CREATE TABLE people (
    id serial PRIMARY KEY,
    firstname TEXT,
    lastname TEXT
);
 
CREATE TABLE visits (
    id serial PRIMARY KEY,
    person_id INT4 NOT NULL REFERENCES people (id),
    when_logged timestamptz
);
 
INSERT INTO people (firstname, lastname)
VALUES
    ('Bill', 'Hicks'),
    ('George', 'Carlin'),
    ('Louis', 'C.K.'),
    ('Robin', 'Williams'),
    ('Zach', 'Galifianakis');
 
INSERT INTO visits (person_id, when_logged)
    SELECT
        1 + floor(random() * 5),
        now() - '1 year'::INTERVAL * random()
    FROM
        generate_series(1,1000);

Теперь положим что надо получить список людей со счётчиками посещений. Ранее надо было делать так:

SELECT
    p.id,
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id;

Или так:

SELECT
    p.id,
    p.firstname,
    p.lastname,
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id, p.firstname, p.lastname;

(или трюкачить с min(firstname))

Но теперь можно сделать так:

SELECT
    p.id,
    p.firstname,
    p.lastname,
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id;

И всё будет прекрасно работать.

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

2010-08-11T19:32:00.008+04:00

Перевод 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 менее навязчивыми.

Ранее все ALTER TABLE блокировали всякий доступ к таблице:

Сессия 1: # begin;
Сессия 2: # begin;
Сессия 1: *# create trigger q after insert on table_name 
            for each row execute procedure test();
...тут засыпает на 10 минут...
Сессия 2: *# select * from table_name;
...а тут ждёт 10 минут...

Это было потому что все ALTER TABLE и CREATE TRIGGER требовали AccessExclusiveLock на таблицу:

*# create trigger q after insert on table_name for each row execute procedure test();
CREATE TRIGGER
 
*# SELECT * FROM pg_locks where pid = pg_backend_pid();
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid  |        mode         | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+---------------------+---------
 virtualxid    |   [null] |   [null] | [null] | [null] | 13/674866  |        [null] |  [null] | [null] |   [null] | 13/674866          | 9492 | ExclusiveLock       | t
 relation      |    48535 |    69187 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 13/674866          | 9492 | AccessExclusiveLock | t
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |        632704 |  [null] | [null] |   [null] | 13/674866          | 9492 | ExclusiveLock       | t
 relation      |    48535 |    10969 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 13/674866          | 9492 | AccessShareLock     | t
(4 rows)

И как мы знаем (или можем найти в документации) данный тип блокировки конфликтует со всем.

В 9.1 полученная блокировка будет другой:

*$ SELECT * FROM pg_locks where pid = pg_backend_pid();
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction |  pid  |         mode          | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+-------+-----------------------+---------
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |           752 |  [null] | [null] |   [null] | 2/386              | 27199 | ExclusiveLock         | t
 relation      |    16398 |    16419 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/386              | 27199 | ShareRowExclusiveLock | t
 virtualxid    |   [null] |   [null] | [null] | [null] | 2/386      |        [null] |  [null] | [null] |   [null] | 2/386              | 27199 | ExclusiveLock         | t
 relation      |    16398 |    10987 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/386              | 27199 | AccessShareLock       | t
(4 rows)
 
(depesz@[local]:5900) 15:12:55 [depesz]
*$ select 16419::regclass, 10987::regclass;
  regclass  | regclass
------------+----------
 table_name | pg_locks
(1 row)

В этот раз мы видим, что CREATE TRIGGER получает только ShareRowExclusiveLock, что разрешает делать SELECT запросы. Операции модификации (UPDATE, INSERT, DELETE) и другие DDL запросы к этой таблице всё ещё не будут разрешены. Но возможность хотя бы делать операции чтения это уже большой выигрыш.

Я не нашел окончательный список всех вариантов ALTER TABLE, которые получают такой уровень блокировки, но это и не так важно - всё ещё поменяется, и новые варианты будут добавляться. Сейчас я знаю что добавление триггеров и установка значения по умолчанию используют ShareUpdateExclusiveLock вместо AccessExclusiveLock. Что будет следующим - посмотрим.

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

2010-08-11T18:56:00.003+04:00

На основе обсуждения 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

2010-07-29T20:39:00.004+04:00

Перевод 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

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

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

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

2010-07-26T13:14:00.006+04:00

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

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

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

Во-первых, добавим тестовых данных:

CREATE TABLE test_data (
    id       INT4 PRIMARY KEY,
    codename TEXT
);
INSERT INTO test_data ( id, codename )
    SELECT i, 'codename for: ' || i
    FROM generate_series( 1, 100 ) i;

Теперь, чтобы получить эти 4 строки, можно сделать так:

SELECT * FROM test_data WHERE id IN (3, 71, 5, 16);
 id |     codename     
----+------------------
  3 | codename FOR: 3
  5 | codename FOR: 5
 16 | codename FOR: 16
 71 | codename FOR: 71
(4 rows)

Но, никак нельзя добавить ничего в ORDER BY, чтобы получить их в порядке указанном в IN (...). Итак, что можно сделать. Один способ это использовать (доступные с 8.2) VALUES() следующим образом:

SELECT
    t.*
FROM
    test_data t
    JOIN (
        VALUES
            (1, 3),
            (2, 71),
            (3, 5),
            (4, 16)
        ) AS c (ordering, id) ON t.id = c.id
ORDER BY
    c.ordering;
 id |     codename     
----+------------------
  3 | codename FOR: 3
 71 | codename FOR: 71
  5 | codename FOR: 5
 16 | codename FOR: 16
(4 rows)

Всё очень хорошо, только надо указывать дополнительные данные, и их формат не очень хорошо выглядит. К счастью есть другой способ. С 8.4 у нас есть Window Functions и unnest(), т.ч. можно сделать так:

WITH ordered_want AS (
    SELECT
        id,
        row_number() over () AS ordering
    FROM
        unnest( '{3,71,5,16}'::INT4[] ) AS id
)
SELECT
    t.*
FROM
    test_data t
    JOIN ordered_want o ON t.id = o.id
ORDER BY
    o.ordering;

Уже лучше. В этом случае подстановка id-ков осуществляется намного проще - просто укажите массив целых и всё.

Есть ещё один способ. Если не можете использовать Window Functions, то можно задействовать относительно старый трюк с упорядочиванием по position():

SELECT
    t.*
FROM
    test_data t
WHERE
    t.id IN (3,71,5,16)
ORDER BY
    position( ',' || t.id || ',' IN ',3,71,5,16,' );

Тут не забудьте указать пердшествующую и последующую запятые и добавьте их также вокруг t.id в ORDER BY.

Да, тут будут проблемы, если вы работаете с текстовыми id, но всёже один из вариантов.

Комментарии:

# AREK, 25 июня 2010

Если поставить intarray, то можно упростить последний запрос следующим образом:

SELECT *, idx(‘{3,71,5,16}’, id) AS rank FROM test_data WHERE id = ANY(‘{3,71,5,16}’) ORDER BY rank;

Заметьте также, что вместо IN тут используется = ANY(). Это может быть проблемой для кода приложения, если вы не пишите запросы сами, а просто подставляете значения.

Что касается текстовых id, то можно написать функцию idx(text[], text) и использовать тот же запрос.

MVC бэкапы

2010-07-26T12:59:00.003+04:00

Перевод 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

2010-07-22T11:29:00.004+04:00

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

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

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

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

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

$ \conninfo

You are connected to database "depesz" via local socket at port "5900" as user "depesz".

Это полезно тем, кто работает с несколькими базами данных - просто для того чтобы по ошибке не запустить SQL не на той базе.

Ранее подобный эффект можно было получить добавив следующее в $HOME/.psqlrc:

\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x$ '

Благодаря этому моя подсказка выглядит так:

(depesz@[local]:5900) 14:57:43 [depesz]
$

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

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

2010-07-22T10:42:00.007+04:00

Перевод 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 стандарт это не приемлет.

Некоторое время назад, не помню когда точно, да и это не так важно, PostgreSQL представил маскированые строковые константы, которые выглядят так:

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

И когда вы используете обычные строки с маскированием обратной косой чертой, он выдаёт предупреждение:

$ SELECT 'guns \'n roses';
WARNING:  nonstandard USE of \' in a string literal
LINE 1: select 'guns \'n roses';
               ^
HINT:  USE '' TO WRITE quotes IN strings, OR USE the escape string syntax (E'...').
   ?COLUMN?    
---------------
 guns 'n roses
(1 row)

Т.е. всё работает так же как и раньше, только добавилось это предупреждение. Конечно же оно (предупреждение) может быть отключено (escape_string_warning GUC), и вы можете запретить маскирование установив standard_conforming_strings в on.

Теперь, в 9.1, standard_conforming_strings будет по умолчанию on. А это значит, что с нашим тестом случится следующее:

$ SELECT 'guns \'n roses';
>>

PostgreSQL теперь трактует \ как обычный символ, и т.к. кол-во апострофов нечётное значит что одна из строк ещё не завержена и запрос ещё не готов.

Простой пример:

$ SELECT 'first line\nsecond line';
  ?COLUMN?
-------------
 first line +
 second line
(1 row)
 
$ SET standard_conforming_strings = ON;
SET
 
$ SELECT 'first line\nsecond line';
        ?COLUMN?
-------------------------
 first line\nsecond line
(1 row)

Итак, как теперь писать запросы с апострофами и символами новой строки? Короткий ответ - делать это правильно. Пример:

$ SELECT 'guns ''n roses';
   ?COLUMN?
---------------
 guns 'n roses
(1 row)
 
$ select E'first line\nsecond line';
  ?column?
-------------
 first line +
 second line
(1 row)

Если вы пишите запросы сами, это не большая проблема, но если используете что-то что строит их за вас (ORM или что-то формирующее параметры) - это должно быть исправлено (если не было исправлено ранее).

Конечно же можно изменить standard_conforming_strings обратно в off, но это не будет нормальным решением.

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

2010-06-07T14:27:00.006+04:00

Если пользуетесь 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 в подробностях

2010-04-27T03:51:00.010+04:00

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

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

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

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

Londiste базируется на механизме очередей PgQ, который тоже входит в пакет SkyTools. Информация об изменениях данных попадает в эти очереди из спец-триггеров, навешиваемых на реплицируемые таблицы. PgQ, в свою очередь, основывается на пакетной обработке и использует так называемый ticker, утилиту (демон), генерирующую события готовности пачек данных. Эти события слушает демон Londiste и по их наступлению переносит пачки с мастера на слейв.

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

Описанный процесс не требует даунтайма.

Замечание:

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

1. Первым делом подготовим мастер-сервер

Замечание:

Пропустите этот пункт если репликация мастер-сервера уже есть.

1.1. Права на мастере

В данном примере все утилиты будут работать под системным пользователем postgres. Операции с БД я доверю также пользователю postgres (который в базе). Но в реальности будет правильнее сделать отдельного пользователя для этих утилит, смотрите по вашей ситуации.

Дадим пользователю postgres доверительный доступ (без пароля) к базам на мастере со слейва.

/var/lib/postgres/8.4/data/pg_hba.conf:

...
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
                                # host2 IP
host    db1         postgres    192.168.10.2/32       trust
...

Если реплицируются конкретные базы, а не весь инстанс, то добавьте такие записи для каждой из них. В случае же репликации всего инстанса достаточно одной записи, где в колонке DATABASE указываем all. Не забудьте сделать reload серверу.

1.2. Настрока ticker-а

Далее создадим файл конфигурации ticker-а.

/etc/skytools/db1-ticker.ini:

[pgqadm]
job_name = db1-ticker
db = dbname=db1

# Задержка между запусками обслуживания (ротация очередей и т.п.) в секундах
maint_delay = 600

# Задержка между проверками наличия активности (новых пакетов данных) в секундах
loop_delay = 0.1
logfile = /var/log/skytools/%(job_name)s.log
pidfile = /var/run/skytools/%(job_name)s.pid

Создайте подобные конфигурации для каждой реплицируемой базы.

1.3. Запускаем ticker

Теперь необходимо инсталлировать служебный код (SQL) и запустить ticker как демона для каждой из баз. Делается это с помощью утилиты pgqadm.py следующими командами:

python pgqadm.py /etc/skytools/db1-ticker.ini install
python pgqadm.py /etc/skytools/db1-ticker.ini ticker -d

Проверим, что в логах (/var/log/skytools/db1-tickers.log) всё нормально. На данном этапе там должны быть редкие записи (раз в минуту).

2. Далее настроим слейв

2.1. Убедимся что Londiste остановлен

Замечание:

Это действие необходимо если вы раньше работали с Londiste на этом слейве.

python londiste.py -s /etc/skytools/db1-londiste.ini

Повторите для всех реплицируемых баз.

2.2. Востанавливаем схему базы

Замечание:

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

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

pg_dump -s -C -h host1 -U postgres db1 | 
psql -U postgres 1>db1-restore.stdout 2>db2-restore.stderr

Если реплицируем инстанс целиком, то достаточно один раз сделать так:

pg_dumpall -s -h host1 -U postgres | 
psql -U postgres 1>all-restore.stdout 2>all-restore.stderr

Убедимся, что в лог-файлах *-restore.stderr отсутствуют ошибки кроме "роль postgres уже существует" (ч.г. не могу понять для чего вообще pg_dumpall дампит эту роль).

2.3. Создаём конфигурацию репликатора

Для каждой из реплицируемых баз создадим конфигурационные файлы:

/etc/skytools/db1-londiste.ini:

[londiste]
job_name = db1-londiste

provider_db   = dbname=db1 port=5432 host=host1
subscriber_db = dbname=db1

# Это будет использоваться в качестве SQL-идентификатора, т.ч. не используйте
# точки и пробелы.
# ВАЖНО! Если есть живая репликация на другой слейв, именуем очередь так-же
pgq_queue_name = db1-londiste-queue

logfile = /var/log/skytools/%(job_name)s.log
pidfile = /var/run/skytools/%(job_name)s.pid

log_size = 5242880
log_count = 3

2.4. Устанавливаем Londiste в базы на мастере и слейве

Теперь необходимо установить служебный SQL для каждой из созданных в предыдущем пункте конфигураций.

Устанавливаем код на стороне мастера:

Замечание:

Если установка Londiste на эту базу мастера уже была то для него не делаем.

python londiste.py /etc/skytools/db1-londiste.ini provider install

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

python londiste.py /etc/skytools/db1-londiste.ini subscriber install

После этого пункта на мастере будут созданы очереди для репликации.

2.5. Запускаем процессы Londiste

Для каждой реплицируемой базы делаем:

python londiste.py /etc/skytools/db1-londiste.ini replay -d

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

Убедимся что в логах нет ошибок (/var/log/skytools/db1-londistes.log).

2.6. Добавляем реплицируемые таблицы

Для каждой конфигурации указываем что будем реплицировать с мастера:

Замечание:

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

python londiste.py /etc/skytools/db1-londiste.ini provider add --all

и что со слейва:

python londiste.py /etc/skytools/db1-londiste.ini subscriber add --all

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

2.7. Добавляем реплицируемые последовательности (sequence)

Так же для всех конфигураций.

Для мастера:

Замечание:

Опять же, если они уже добавлены на мастере, то не добавляем их там.

python londiste.py /etc/skytools/db1-londiste.ini provider add-seq --all

Замечание:

В версиях SkyTools до 2.1.9 включительно был обнаружен баг при добавлением последовательностей на мастере используя параметр --all, т.ч. если у вас такая версия используйте следующий хак:

for seq in $( psql -t -A -U postgres toozla -c "\ds" | awk -F '|' '{ if ($1 != "pgq" && $1 != "londiste") { print $1"."$2 } }'); do python londiste.py /etc/skytools/db1-londiste.ini provider add-seq $seq; done

Для слейва:

python londiste.py /etc/skytools/db1-londiste.ini subscriber add-seq --all

Точно также как и с таблицами можно указать конкретные последовательности вместо --all.

2.8. Проверка

Итак, всё что надо сделано. Теперь Londiste запустит так называемый bulk copy процесс, который массово (с помощью COPY) зальёт присутствующие на момент добавления таблиц данные на слейв, а затем перейдёт в состояние обычной репликации.

Мониторим логи на предмет ошибок:

less /var/log/skytools/db1-londiste.log

Если всё хорошо, смотрим состояние репликации. Данные уже синхронизированы для тех таблиц, где статус отображается как "ok".

python londiste.py /etc/skytools/db1-londiste.ini subscriber tables

Table                                           State
public.table1                                      ok
public.table2                                      ok
public.table3                                 in-copy
public.table4                                       -
public.table5                                       -
public.table6                                       -
...

Для удобства представляю следующий трюк с уведомление в почту об окончании первоначального копирования (мыло поменять на своё ;):

(
  while [ $(
    python londiste.py /etc/skytools/db1-londiste.ini subscriber tables |
    tail -n+2 | awk '{print $2}' | grep -v ok | wc -l) -ne 0 ];
  do sleep 60; done; echo '' | mail -s 'Replication done EOM' user@domain.com
) &

Ссылки на публикации, которые мне помогли в написании этой статьи:

SkyTools - PostgreSQL Wiki
http://wiki.postgresql.org/wiki/Skytools

SkyTools - Skype Developer Zone
https://developer.skype.com/SkypeGarage/DbProjects/SkyTools

PostgreSQL master slave(s) asynchronous replication - tail -f /dev/dim
http://tapoueh.org/skytools.html

Перенимаем NoSQL

2010-04-14T22:48:00.004+04:00

Перевод 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

2010-04-14T22:14:00.003+04:00

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

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

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

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

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

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

2010-04-12T10:33:00.005+04:00

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

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

Описание:

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

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

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

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

2010-04-07T20:09:00.006+04:00

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

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

В Linux это делается просто встроенной командой "ln -s ...", а под Windows для их создания будет нужна вот эта программа (Junction).

Итак, опишу сам процесс по шагам. Допустим директория pgdata на диске D, и необходимо вынести WAL (pg_xlog) на диск C.

Для этого надо:

1. Остановить postgres
2. Сделать бэкап D:\pgdata\pg_xlog
3. Скопировать D:\pgdata\pg_xlog в C:\pg_xlog и удалить D:\pgdata\pg_xlog
4. Распаковать программу junction в D:\pgdata
5. Открыть окно cmd, перейти там в D:\pgdata и выполнить junction -s pg_xlog C:\pg_xlog
6. Теперь кликаем правой кнопкой на папке C:\pg_xlog и устанавливаем права на неё пользователю postgres
7. Запускаем postgres

Вот и всё.

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

2010-04-05T19:36:00.003+04:00

Требуется расширение 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;