SQL: пара приемов в SELECT-запросах

Забавы ради попробовал решить, не подглядывая в ваш вариант (PostgreSQL).
Создаем таблицу с данными (данные неупорядоченные)

create table weather(time timestamp, clear boolean);

insert into weather(time, clear)
select generate_series(
  '2000-01-01'::timestamp, 
  '2009-01-01'::timestamp, 
  random() * 7 * '1 day'::interval
) as time,
random() > 0.5 as clear order by random();

Находим интервалы солнечных дней (начало, конец и длина), упорядочиваем по длине и выбираем 20.

  with ordered_weather as (select * from weather order by time),
  nasty as (
    select row_number() over() as gap_group, time, clear from ordered_weather where not clear
  ),
  gaps as (
    select row_number() over(), b.gap_group, a.time, a.clear from ordered_weather a
    left join nasty b using (time, clear)
  ),
  limited_gaps as (
    select 0 as row_number, 0 as gap_group, 
    (select min(time) - '1 day'::interval from weather) as time, false as clear
    union all
    select * from gaps
    union all
    select (select max(row_number) + 1 from gaps), (select max(gap_group) + 1 from gaps), 
    null::timestamp, false
  )
  select c.time as clear_start, d.time as clear_stop, (d.time - c.time) as clear_interval 
  from limited_gaps a
  left join (
    select row_number, gap_group + 1 as gap_group from limited_gaps where gap_group is not null
  ) b using(gap_group)
  join limited_gaps c on c.row_number = coalesce(b.row_number,0) + 1
  join limited_gaps d on d.row_number = a.row_number - 1
  where a.gap_group is not null and (a.row_number - coalesce(b.row_number,0) > 1)
  order by (d.time - c.time) desc limit 20;

Техника безопасности при работе с PostgreSQL

Я внес замечания в статью по поводу with, deferrable и set autocommit off. Спасибо большое за замечания и советы.

Техника безопасности при работе с PostgreSQL

Поправил, это и правда было написано плохим стилем.

Техника безопасности при работе с PostgreSQL

Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов)

А можно поподробнее про подводные камни на 100 ГБ? У меня БД на 60 ГБ, хочу узнать, к чему готовиться через пару лет.

Техника безопасности при работе с PostgreSQL

Если количество клиентов гарантированно останется небольшим (десяток-другой) и увеличиваться никогда не будет, то да. Главное, не ошибиться.

Техника безопасности при работе с PostgreSQL

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

Техника безопасности при работе с PostgreSQL

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

Техника безопасности при работе с PostgreSQL

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

Техника безопасности при работе с PostgreSQL

И не просто наступал, но и героически преодолевал последствия. До сих пор вздрагиваю.

Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др

Посмотрел на теги внизу и нашёл родственную душу — я всегда делаю такую же ошибку в написании слова deferrable ;) и только написав вспоминаю, что где-то был капкан.

Вы не любите триггеры?

Проблема в том, что использовать базу для аутентификации пользователей — плохая идея, этим должен заниматься сервер. Как я понял, вы решили схитрить и просто прокидывать пользователя к БД, а она пусть штатными средствами и разбирается кто есть кто и что ему можно. Это неправильная архитектура по причине отсутствия возможности для маштабируемости. Если у вас резко увеличится количество пользователей, база ляжет. И никакие балансировщики нагрузки БД вам не помогут, так как каждый пользователь будет со своим подключением, и их нельзя будет собрать в единый пул.
Вам не нужно поддерживать соответствие между пользователями в БД и на сервере. В простейшем случае в базе из пользователей будут владелец базы и пользователь для пула коннектов, под которым все ходят. У этого пользователя не будет прав на таблицы, только на выполнение функций с security definer. В каждой транзакции сервер должен выставлять

begin;
select set_config('ваша_переменная_пользователя_из_pg_config', 'пользователь_на_сервере', false);
select func(...);
commit;

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

select current_setting('ваша_переменная_пользователя_из_pg_config') into username;

А дальше мы и смотрим, может ли данный username использовать вызванную функцию. Но эта проверка должна быть на уровне некой дополнительной функции, а не через пользователей PostgreSQL.

Вы не любите триггеры?

Базы данных держат сотни подключений, а не тысячи, как web сервера. И каждое простаивающее подключение съедает ресурсы. Именно поэтому никто взаимооднозначно не транслирует клиентов через сервер в базу, а заворачивают кучу пользователей в один пул. Иначе база не потянет. Поэтому штатными средствами acl базы вы сможете раздать права на выполнение функций только учеткам, которые используются в пуле. Не штатными — можно использовать GUC (в случае PostgreSQL), выставляя на сервере в каждой транзакции имя пользователя через set_config. А в функциях вытаскивать из глобальной переменной имя пользователя и делать проверку.