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

В итоге набрёл на весьма неплохой инструмент pgModeler. Единственное, не очень понравилось, что sql-скрипты он умеет генерировать только для PostgreSQL. Но т.к. на тот момент (да и сейчас, а то и потом) использовалась эта база данных, то этого инструмента было вполне достаточно.

PostgreSQL 10 был выпущен еще в начале октября 2017го, почти год назад.

Одна из наиболее интересных новых “фич” — это безусловно декларативное партиционирование. Но что, если вы не спешите апгрейдится до 10ки? Вот Amazon, к примеру, не торопится, и ввел поддержку PostgreSQL 10 только в последних числах февраля 2018-го.

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

Поскольку мы начали переписывать нашу финансовую систему в 2015ом, когда я только присоединился к компании, ни о каком декларативном партиционировании речи не шло. Так что и по сей день успешно используется методика описанная ниже.

Изначальной причиной написания статьи стало то, что большинство примеров partitioning’а в PostgreSQL с которыми я сталкивался были очень базовыми. Вот таблица, вот одна колонка, на которую мы смотрим, и быть может даже заранее знаем, какие значения в ней лежат. Казалось бы, все просто. Но реальная жизнь вносит свои коррективы.

Приветствую всех любителей SQL!

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

Представленные ответы подходят для PostgreSQL (большинство задач подойдут и для других СУБД, но результаты и решения могут быть иными. Даже интересно, где возникнут отличия)

Постарайтесь ответить самостоятельно, перед открытием спойлера.

Поехали!

Вступление

В стандарте SQL описывается четыре уровня изоляции транзакций — Read uncommited (Чтение незафиксированных данных), Read committed (Чтение зафиксированных данных), Repeatable read (Повторяемое чтение) и Serializable (Сериализуемость). В данной статье будет рассмотрен жизненный цикл четырёх параллельно выполняющихся транзакций с уровнями изоляции Read committed и Serializable.

Для уровня изоляции Read committed допустимы следующие особые условия чтения данных:

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

Фантомное чтение — транзакция повторно выполняет запрос, возвращающий набор строк для некоторого условия, и обнаруживает, что набор строк, удовлетворяющих условию, изменился из-за транзакции, завершившейся за это время.

Что же касается Serializable, то данный уровень изоляции самый строгий, и не имеет феноменов чтения данных.

Привет, Хабр! Мы в Хекслете запустили в этом году открытый и бесплатный проект для новичков: code-basics.ru. Это интерактивные курсы с практикой, рассчитанные на тех, кто вообще никогда не программировал. Мы учим основам программирования и рассказываем про базовое устройство компьютеров.

Сейчас доступен полный курс по PHP и первый модуль по JavaScript. На подходе Racket и Python. Весь исходный код платформы и самих уроков — в open source.

Сегодня хочу рассказать немного о сути Code Basics, его внутренностях и технологическом стеке.

Ранее я писал, как включить поддержку jsonb в postgres/psycopg2. Сегодня экспериментировал с тем, как запрашивать данные в колонках типа JSON.
На эту тему есть документация, но мне было не совсем понятно, как работают различные операции:

CREATE TABLE json_test (
  id serial primary key,
  data jsonb
);

INSERT INTO json_test (data) VALUES 
  ('{}'),
  ('{"a": 1}'),
  ('{"a": 2, "b": ["c", "d"]}'),
  ('{"a": 1, "b": {"c": "d", "e": true}}'),
  ('{"b": 2}');

Почему запрос выполняется так долго? Почему не используются индексы?
Наверное, все слышали об EXPLAIN в PostgreSQL. Но не так много тех, кто понимает, как его использовать. Сам длительное время не мог найти доступного для понимания учебника (плохо искал?).
Надеюсь, эта статья поможет желающим разобраться с этим замечательным инструментом.

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

Как известно, чтобы изучать и не забывать язык, им надо пользоваться. И один из способов практиковать язык — это начать смотреть фильмы в оригинале.
Но как можно начать смотреть фильмы и получать при этом удовольствие, если в каждом диалоге актеров встречаются незнакомые слова?

В интернете полно методик, где советуют, например, смотреть по 3 раза или подготавливаться к просмотру, выписывая и заучивая заранее слова из субтитров. Да, возможно это эффективно, и кому-то подойдут такие способы, но это же так скучно… Я бы не смог смотреть 3 раза подряд один и тот же фильм. Хочется получать удовольствие от просмотра, смотреть сразу и без подготовки и все понимать.

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

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

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

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin'e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

Поехали!

Эта статья также есть на английском.

Однажды вечером, перечитывая Джеффри Фридла, я осознал, что даже несмотря на всем доступную документацию, существует множество приемов заточенных под себя. Все люди слишком разные. И приемы, которые очевидны для одних, могут быть неочевидны для других и выглядеть какой-то магией для третьих. Кстати, несколько подобных моментов я уже описывал здесь.

Командная строка для администратора или пользователя — это не только инструмент, которым можно сделать все, но и инструмент, который кастомизируется под себя любимого бесконечно долго. Недавно пробегал перевод на тему удобных приемов в CLI. Но у меня сложилось впечатление, что сам переводчик мало пользовался советами, из-за чего важные нюансы могли быть упущены.

Под катом — дюжина приемов в командной строке — из личного опыта.

Как ни странно, чтобы понять рекурсию, в PostgreSQL не надо понимать рекурсию. Потому что WITH RECURSIVE, который присутствует в посгресе (и в других серьёзных базах) — это скорее вычисление чего-то итерациями до того, как будет выполнено некоторое условие.
Тем не менее это очень полезный функционал базы, который можно использовать, например, чтобы вывести все подкатегории заданной категории, если таблица задана в виде (id, parent_id, ...)

Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.

В этом отрывке рассмотрены команды управления процессами. Вы научитесь замораживать процессы, размораживать, отправлять в фоновый режим, изменять приоритет, просматривать запущенные процессы и жестоко их убивать. Введено понятие сигналов. Рассмотрены такие команды, как bg, fg, jobs, kill, nohup, nice, renice, ps и top.

Навигация по основам Linux от основателя Gentoo:

Часть I

1. BASH: основы навигации (вступление)
2. Управление файлами и директориями
3. Ссылки, а также удаление файлов и директорий
4. Glob-подстановки (итоги и ссылки)

Часть II

1. Регулярные выражения (вступление)
2. Назначения папок, поиск файлов
3. Управление процессами
4. Обработка текста и перенаправления
5. Модули ядра (итоги и ссылки)

Доброго времени суток! Не за горами релиз PostgreSQL 9.4 и нелишним будет пройтись по некоторым новшествам, привнесённым в этой версии. В статье будут рассмотрены (по большей части, касаемые клиентской модели):

• изменения в автообновляемых представлениях
• изменение в материализированных представлениях
• команда ALTER SYSTEM для установки конфигурационных параметров
• опция WITH ORDINALITY для функций, возращающих набор строк
• новые функции для агрегированных данных
• улучшения для индексов GIN и GiST
• улучшенный вывод команды EXPLAIN
• разогрев кэша
• триггеры для внешних таблиц
• изменения для json и новый тип данных jsonb

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

PostgreSQL, как и любая другая СУБД, умеет делать некоторые проверки при вставке/изменении данных, и этим обязательно нужно уметь пользоваться. Давайте посмотрим, что мы можем проверять:

1. Кастомный подтип через ключевое слово DOMAIN