Задачи от Postgres Professional

          Когда мы, программисты, думаем (и говорим) о себе, мы всегда представляем себя интеллектуальными личностями, которые постоянно вынуждены заниматься самосовершенcтвованием и повышением квалификации, ведь этого (и формально так оно и есть) требует от нас постоянно меняющийся мир информационных технологий. Несмотря на эти красивые слова, многие из нас (так, нас точно читают только программисты или здесь и шпионы затесались?) в реальности киснут на монотонной и стереотипной работе, требующей скорее опыта, старого багажа знаний и представления о том, где можно найти решение проблемы, буде она у нас вдруг возникнет. С каждым годом, прожитым послe окончания института, нашему мозгу все меньше хочется узнавать новое, переосмысливать узнанное и искать новые пути решения проблем, но это не значит, что мы не должны периодически его заставлять работать на повышенных оборотах. Еще как должны! Мозг тоже мышца :), и без тренировки его функции будут ухудшаться и ухудшаться. Поэтому решай наши задачки ежемесячно, и твой мыслительный орган навсегда останется быстрым, мощным и извилистым :).

          Компания, которая участвует в сегодняшнем выпуске, тебе точно известна — как минимум по одному слову в названии. Слово это — Postgres.

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

Как проходит собеседование в компании Postgres Professional

          У нас нет многоступенчатых собеседований, психологических тестирований, словом, мы не мучаем кандидатов формaльностями, как это встречается в крупных компаниях. Когда человек, который хочет работать в Postgres Professional, приходит на собеседование, с ним разговаривают сразу несколько сотрудников: начальник отдела, куда кандидат устраивается на работу, HR-специалист, представитель высшего руководства, иногда — основатели компании. Решение принимается после одного-двух собеседований, на них обсуждаются различные профессиональные вопросы, в итоге становится понятен и уровень квалификации соискателя, и подходим ли мы друг другу для командной работы.

          Конечно, с кандидатами в разработчики мы не просто разговариваем, а даем каждому решить одну-две задачи вроде тех, что предложены сегодня в этой рубрике. И обязательно задаем вопросы практической направленности, предлагаем ответить на типичный клиентский запрос. Какими могут быть эти практические задания? Например, компания оказывает услуги по техподдержке PostgreSQL и нашей собственной разработки — СУБД Postgres Pro. Обычно для систем, которые создаются на базе свободного ПО, поддержка — вопрос проблемный. В России мы единственные, кто ее предоставляет, и для нас это — дело большой важности. Для техподдержки в Postgres Professional выделены специальные сотрудники, а остальные разработчики могут подключаться, когда у клиентов возникают проблемы. Для каждого соискателя мы моделируем ситуацию вроде тех, которые встречаются у заказчиков, и просим найти решение.

Задачи от Postgres Professional

Задача 1

          Есть таблицы пользователи usr(id int,name text), друзья friend(usr_id int, friend_usr_id int) и посты post(id int, usr_id int, content text, added timestamptz). Напишите максимально эффективный запрос, возвращающий десять последних постов друзей заданного пользователя (могут быть многочисленные друзья с большим количеством постов в прошлом).

Задача 2

          Можно ли в строке, состоящей из символов ( и ), проверить баланс скобок? Как?

Задача 3

          Есть большая таблица работников employee(id int, occupation text, sex text). Так получилось, что таблица состоит исключительно из бульдозеристов-мужчин и нянечек-женщин. При эксплуатации выяснилось, что иногда запрос

select 'found' where exists (
  select * from employee
  where occupation=$1 and sex=$2
)

работает очень медленно. Когда это происходит и как этого можно избежать?

Задача 4

          Есть таблицы parent(id int) и child(id int, parent_id int references parent(id)). Накладываются ли какие-либо блокировки на строки таблицы parent при добавлении строк в таблицу child? Если да, то какие? Если нет, то почему?

Задача 5

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

select sum(
  case when type='debet'  then  amount
       when type='credit' then -amount
  end
) from ledger

Задача 6

          Почему данный запрос, обновляющий или добавляющий строки в случае их отсутствия, некорректен?

with upd as(
  update tusr set name='name2' where id=1 returning 1
),
ins as(
  insert into tusr(id,name)
  select 1, 'name'
  where not exists(select * from upd)
  returning 2
)
select * from ins

Задача 7

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

create or replace function check_uniq() returns trigger as
$code$
begin
  if exists (select * from tbl t where t.col=new.col) then
    raise exception 'Unique violation';
  end if;
end;
$code$
language plpgsql

Почему такой триггер некорректен? Как его сделать корректным?

Задача 8

          Какое сообщение выведет этот блок кода?

do $code$
declare
  ctx text;
begin
  raise sqlstate 'ER001';
exception
  when sqlstate 'ER000' then
    raise notice 'ER000';
  when sqlstate 'ER001' then
    raise notice 'ER001';
end;
$code$

Читать продолжение: