PostgreSQL *

Postgresql, без сомнения, великолепная СУБД. Она обладает обширнейшими возможностями, отличной документации, и при всем при этом является бесплатной. Однако, всегда найдется что-то, чего пользователю не будет хватать. И в postgresql это легко исправляется, ведь он позволяет создавать функции на языках на любой вкус, будь то Plpgsql, Perl или даже Java.

Приведу пример. Мне всегда не хватало функции, получающей DDL выбранной таблицы. В oracle, например, вы можете воспользоваться для этого средствами пакета dbms_metadata. А вот в postgresql аналога почему-то нет. То есть можно конечно использовать pgdump, но это уже немного не то, мне хотелось бы иметь функцию бд. И так далее, думаю у каждого найдется несколько таких небольших «хотелок».

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

В этом топике я хочу рассказать о задаче, очень часто встречающейся в веб-проектах — определение местоположения по ip-адресу. Начну с того, что для того, чтобы определить местоположение пользователя — нужна некая geoip база. Приведу здесь два популярных бесплатных варианта:
IpGeoBase — очень хорошая бесплатная база, но, к сожалению, только по российским ip-адресам.
MaxMind — огромная база по ip-адресам всех стран. Предоставляют бесплатную lite-версию базы. Точность базы по российским ip-адресам не настолько хороша, как у IpGeoBase. Также предоставляют некое API для работы со своей базой, которое позволяет производить выборки очень быстро.

Допустим вы скачали эти базы и залили их в таблицы вашей БД Postgresql (сама закачка — несколько out of scope, если у кого-то возникнет желание — я могу в будущем рассказать о том, что такое команда COPY и с чем ее едят). В общем случае вы получите таблицу такой структуры:

startip	endip	location_id
2130706433	2130706433	1

Здесь:
startip — это начало блока ip-адресов в формате long
endip — конец блока ip-адресов в формате long
location_id — идентификатор локации (город, регион, страна и т.д., maxmind даже координаты содержит).

Каким бы супер-пупер спецом вы не были — в процессе разработки эпизодически попадаются интересные и удобные вещи о которых, казалось бы, давно бы пора знать, но все никак не складывалось. Этакий ништячок, найдя который хочется воскликнуть: «Эврика!». Вот таким ништячком для меня стал элемент синтаксиса INSERT в PostgreSQL, которым я и хочу поделиться с вами.

Ситуация: После добавления записи в базу нужно получить id этой самой свеже-вставленной записи.
Решение на PostgreSQL: INSERT… RETURNING id где id — это PRIMARY_KEY в таблице.

Просто и элегантно, не правда ли?

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

Кому интересно, велкам: doc.prototypes.ru/database/postgresql/contrib/dblink

Родная документация тут: www.postgresql.org/docs/8.4/static/dblink.html

В MySQL можно делать INSERT… ON DUPLICATE KEY UDPATE. В постгресе пока что такого нет (насколько мне известно, MERGE не вошел в версию 8.4).

Я натыкался в сети на рекомендацию использовать правила для того чтобы добиться аналогичного поведения. Мне показалось, что использовать правила в данном случае даже лучше, нежели MERGE, я сделал правило… и наткнулся на подводный камень. Я реализовывал счетчик: есть уникальный ключ и, если такой ключ в таблице уже существует при вставке новой записи — следует вместо этого увеличить значение счетчика.

Простая ситуация, простое правило. Но: при первой вставке это правило, как оказалось, тоже отрабатывает! В результате, при первой вставке вставляется не 1, а 2!

Пока писал, Fred84 подкинул ссылку, где об этой ситуации подробнее рассказано: archives.postgresql.org/pgsql-bugs/2007-03/msg00080.php

Эти инструкции предполагают, что домашняя дерриктория pgsql расположена в /usr/local/pgsql, и данные находится в /usr/local/pgsql/data.

Если ваша конфигурация другая, замените соответствующие пути на свои. Внутренний формат хранения данных типично изменяется в каждом главном выпуске PostgreSQL. Поэтому, если Вы обновляете существующую установку, младше версии “8.3.x”, Вы должны сделать резервную копию, а затем восстановить данные. Если Вы обновляете PostgreSQL “8.3.x”, новая версия может использовать Ваши текущие файлы с данными, таким образом Вы сможете пропустить этапы резервного копирования и восстановления.

Задача

Как удобно делать выборки из деревьев типа Nested Sets, и как не удобно им управлять. Как удобноуправлять деревьями типа id->parent_id, но как не удобно и накладно использовать рекурсии при выборках. Понятно, что прииспользовании модулей для управления деревьями часть проблемы снимается, но при этом процесс работы с базой данных несовсем прозрачен т.е. для изменения данных мы используем одни методы, для изменения расположения узла в дереве — другие,плюс еще транзакции не помешали бы. Эту нестыковку можно решить двумя способами:

• Использовать для работы с таблицей хранимые процедуры, в которой объединить оба метода обновления (вставки, удаления);
• Использовать триггеры, для исключения вообще каких-либо нестандартных методов работы;

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

Сегодня вышел PostgreSQL 8.4

Основные изменения:
* Windowing Functions
* Common Table Expressions and Recursive Queries
* Default and variadic parameters for functions
* Parallel Restore
* Column Permissions
* Per-database locale settings
* Improved hash indexes
* Improved join performance for EXISTS and NOT EXISTS queries
* Easier-to-use Warm Standby
* Automatic sizing of the Free Space Map
* Visibility Map (greatly reduces vacuum overhead for slowly-changing tables)
* Version-aware psql (backslash commands work against older servers)
* Support SSL certificates for user authentication
* Per-function runtime statistics
* Easy editing of functions in psql
* New contrib modules: pg_stat_statements, auto_explain, citext, btree_gin

8.4 Release Notes, PostgreSQL 8.4 Feature List

UPD: русскоязычный обзор на opennet.ru

DB_Pgsql_Type — это фреймворк для преобразования сложных типов PostgreSQL 8.3+ в их аналоги на PHP и обратно. С ее помощью вы можете работать с полями сложного типа (к примеру, двумерным массивом композитных типов) так же просто, как с привычными массивами PHP.

Поддерживаются следующие типы данных и любые их вложенные комбинации:

• Массивы элементов произвольного типа (в том числе многомерные).
• Композитные типы и ROWTYPE (в частности, сами содержащие композитные поля или поля-массивы).
• Hstore (в том числе содержащие сложные элементы).
• Прочие типы: TIMESTAMP (преобразуется в Unix time), DATE, TIME, BOOLEAN и т. д.

Предмет описываемой проблемы

При работе с базой данных в PostgreSQL необходимо не забывать, в какой локали (locale) был инициализирован кластер БД — так в постгре называется директория (обычно /var/lib/pgsql/data), в которой хранятся данные всех баз этой установки PostgreSQL.

Очень тяжело начать писать статью. Т.е очень тяжело придумать вступительное слово. Хочется рассказать обо всём и сразу :) Но нет. Будем последовательны.
Начну с того что совсем недавно проходил Highload++ 2008 на котором мне удалось побывать.
Скажу сразу — мероприятие было проведено по высшему клаcсу, докладов было много и все были очень интересными.
Одной из самых запомнившихся презентаций была лекция Аско Ойя об инфраструктуре серверов баз данных в Skype. Лекция в большей степени касалась различных средств с помощью которых достигается такая производительность серверов.
По словам Аско, база данных Skype выдержит даже если все жители Земли захотят подключится к скайп в один момент.

Приехав домой очень захотелось это всё попробовать в живую. О чём я сейчас и расскажу. Сразу оговорюсь — структура базы данных для теста, взята из примера на сайте самих разработчиков и естественно не имеет ничего общего с реальной загрузкой.
В статье будет описано что распределением нагрузки надо заниматься после того как уже припекло и база падает, но это не совсем так. С помощью данной статьи я как раз хочу подготовить начинающих и не опытных разработчиков и заодно заставить их задуматься о том, что предусматривать возможность распределения нагрузки между серверами надо ещё при проектировании системы. И это не будет считаться той самой «преждевременной оптимизацией» о которой так много пишут и которой так боятся.

UPD: Как правильно заметил хабраюзер descentspb в статье присутствует досаднейшая ошибка. В следствие своей невнимательности я подумал что PgBouncer надо устанавливать между прокси и клиентом. Но, как оказалось, та проблема которую я решал с помощью PgBouncer не решится если установить его именно так. Правильнее надо устанавливать боунсер между нодами и прокси. Мало того, именно так и рекомендуется делать в оффициальном мануале на сайте PL/Proxy.
В любом случае использование PgBouncer так как указано на моей схеме также даст прирост производительности. (Разгрузит Proxy).

Возникла задача построения полнотекстового индекса. В индексируемой таблице было создано поле fts типа ts_vector. При вставке и обновлении триггер, при необходимости, должен обновлять поле fts. Чтобы полнотекстовый индекс не обновлялся при обновлении полей не связанных с ним было установлено условие:

IF (TG_OP = 'UPDATE') AND (NEW."name" = OLD."name")
    THEN RETURN NEW;
END IF;

Т.е. если имя не обновилось мы ничего не трогаем и просто завершаем выполнение триггера. Но при обновлении данный триггер сваливался с ошибкой «record „old“ is not assigned yet». Однако ошибки не возникает при такой конструкции:

IF (TG_OP = 'UPDATE') THEN 
    	IF (NEW."name" = OLD."name") THEN
	       	RETURN NEW;
        END IF;
END IF;

Следовательно, при вычисленнии логического выражения, вычисляется каждая его составляющая. И даже если TG_OP != 'UPDATE' (а это значит выполняется INSERT ) проверяется второе условие где OLD не определён. Отсюда и возникает ошибка.

Недавно у меня возникла интересная задача по хранению некоторых данных в рамках сессии работы с БД PostgreSQL (TTL = время жизни единичного коннекта к базе). Изначальный вопрос был таков…
А можно ли вместо вот такой конструкции:

some_procedure1(user_id, param1, ... , paramN);
...
some_procedureX(user_id, param1, ... , paramN);

использовать такую:

set_user(id);
some_procedure1(param1, ... , paramN);
....
some_procedureX(param1, ... , paramN);

т.е. использовать некую глобальную переменную в рамках сессии для хранения значение идентификатора пользователя, которое будет доступно всем процедурам внутри базы.
Порывшись в гугле, поспрашивав на форуме, я нашел даже не одно решение, а целых 3! Чем с вами и делюсь…

Компания «Постгресмен» и организационный комитет конференции Highload++ рады представить вашему вниманию мастер-класс «Как спроектировать масштабируемую архитектуру PostgreSQL», который проведут эксперты компании Skype Аско Оя (Asko Oja) и Марко Креэн (Marko Kreen). Мероприятие пройдёт 8 октября 2008 года в Москве в конференц-центре «Инфопространство».

После летнего перерыва рады объявить о том, что 16-го сентября (вторник) в Москве планируется очередная, сентябрьская встреча всех тех, кто использует, начинает использовать или просто интересуется PostgreSQL. На этот раз мы поговорим о полнотекстовом поиске PostgreSQL, о том, как правильно его настраивать (прежде всего, при работе с русским и английским языками), о всех многочисленных возможностях FTS в PostgreSQL версии 8.3. С докладом-демонстрацией «Возможности полнотекстового поиска PostgreSQL» выступят Фёдор Сигаев (разработчик FTS в PostgreSQL) и Иван Золотухин. А об интересных новых разработках движка полнотекстового поиска и о планах расскажет Олег Бартунов (также разработчик FTS).

Как всегда, вход свободный, но из-за пропускного режима и ограниченного количества мест требуется предварительная регистрация: http://blog.styleru.net/register/.

Чуть больше подробностей доступно на странице http://postgresmen.ru/news/view/113.

Приглашаем всех желающих!

Многим при реализации поиска по сайту приходится делать запрос к базе данных, использующий оператор LIKE или регулярные выражения.
С выходом PostgreSQL 8.3 модуль полнотекстового поиска tsearch2 был встроен в ядро системы (об этом ранее уже писалось на Хабре), и с его помощью можно реализовать поиск по базе данных, который будет более функционален. В принципе, в статье, указанной выше, приведен пример использования этого модуля, но хотелось бы поделиться опытом реализации полнотекстового поиска в реальном проекте.

30 июня мы провели онлайн-сессию «Вопросы к ведущим мировым экспертам PostgreSQL». В течение трёх часов любой желающий мог задать вопрос одному из приглашённых гостей и получить исчерпывающий ответ. В результате было получено более 40 вопросов, ни один из которых не остался без внимания.

Участники: Брюс Момджан (Bruce Momjian) (PGDG, EnterpriseDB), Максим Богук (Rambler, Мастерхост), Фёдор Сигаев (PGDG), Марко Креэн (Marko Kreen) (Skype).

Вот избранные вопросы и ответы:

Вопрос №3

kaikai:
Одна из досадных штук в работе — это отсутствие конструкции «INSERT OR UPDATE». В MySQL можно сделать так: «INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com') ON DUPLICATE KEY UPDATE email = 'jo@email.com'». Появится ли что-нибудь похожее в PostgreSQL?

Брюс Момджан, лидер сообщества PostgreSQL, эксперт компании EnterpriseDB:
Стандартный (ANSI) способ для таких вещей — это MERGE или UPSERT. Оба способа находятся у нас в TODO-листе и, насколько я знаю, кто-то работает над этой функциональностью для PostgreSQL 8.4, хотя лично я ещё не видел этого патча. Мы, вне всяких сомнений, нуждаемся в таком синтаксисе.

Начался предварительный приём вопросов для Q&A-сессии с ведущими
экспертами PostgreSQL. Начало основного времени: 15:00.

Участники:

* Брюс Момджан (Bruce Momjian), координатор разработки PostgreSQL,
лидер сообщества PostgreSQL, эксперт компании EnterpriseDB.
* Максим Богук, ведущий администратор баз данных компаний Rambler и
Мастерхост, известный специалист по PostgreSQL.
* Фёдор Сигаев, разработчик подсистемы полнотекстового поиска, систем
индексации GiST, GIN и множества дополнительных модулей, один из
основных членов PostgreSQL Global Development Group.
* Олег Бартунов, один из основных членов PostgreSQL Global Development
Group, разработчик подсистемы полнотекстового поиска, систем
индексации GiST, GIN, разработчик многотерабайтных научных баз данных.
* Марко Крин (Marko Kreen), один из основных архитекторов баз данных
компании Skype, разработчик и мантейнер таких проектов как PL/Proxy,
Skytools, PgBouncer и pgcrypto.

Вопросы принимаются в jabber-канале postgresmen@conference.jabber.org
(основной способ) и в skype:postgresmen.

Если вы по каким-либо причинам не можете воспользоваться jabber или
skype, оставляйте вопросы в виде комментариев к этой новости.

Как задавать вопросы:

30 июля компания «Постгресмен» организует онлайн-конференцию «Вопросы к ведущим экспертам PostgreSQL». Любой желающий сможет задать свой вопрос, связанный с внедрением, эксплуатацией, развитием PostgreSQL, всемирно известным экспертам PostgreSQL, ведущим разработчикам и лидерам сообщества PostgreSQL.

Участники конференции:

* Брюс Момджан (Bruce Momjian), координатор разработки PostgreSQL, лидер сообщества PostgreSQL, эксперт компании EnterpriseDB.
* Максим Богук, ведущий администратор баз данных компаний Rambler и Мастерхост, известный специалист по PostgreSQL.
* Фёдор Сигаев, разработчик подсистемы полнотекстового поиска, систем индексации GiST, GIN и множества дополнительных модулей, один из основных членов PostgreSQL Global Development Group.
* Олег Бартунов, один из основных членов PostgreSQL Global Development Group, разработчик подсистемы полнотекстового поиска, систем индексации GiST, GIN, разработчик многотерабайтных научных баз данных.

Начало конференции:

На работе в новом проекте используется СУБД PostgreSQL. Так как до сих пор я работал с MySQL, сейчас приходится изучать и открывать для себя Постгри. Первая проблема, которая меня заинтересовала — замена мускулевского SQL_CALC_FOUND_ROWS. При использовании этой константы в MySQL можно получить количество всех найденных по запросу записей, даже если запрос с limit'ом — это незаменимо при постраничном выводе поисковых результатов, когда используются «тяжелые» запросы.
Сходу готового решения найти не удалось. На форумах просто констатировали, что SQL_CALC_FOUND_ROWS в Постгри нет. Некоторые писали, что надо юзать count(*). И больше никакой информации. Но еще из MySQL мне было известно, что поиск с count()-запросом работает почти в 2 раза медленнее, чем с SQL_CALC_FOUND_ROWS. Я консультировался у тех, кто пользуется PostgreSQL, день мучал google и в результате получил 4 варианта замены SQL_CALC_FOUND_ROWS в PostgreSQL, один из которых вполне приемлимый по скорости.