Akina

Когда каскадное обновление это плохо?

Каскадное обновление - в большинстве случаев это... глупо.

Вспомним, что это вообще такое.

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

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

Что же есть каскадное обновление? Это изменение связанного значения в подчинённой таблице, если изменяется значение основной таблицы. Ну то есть если изменяется (вспоминаем сказанное выше) значение первичного ключа или поля, объявленного уникальным. В основной таблице. Ага...

Ну то, что изменение/корректировка значения поля первичного ключа есть bad practice (читай - дурь голимая), хорошо известно, обосновано и весьма логично. Нет, реально возможны ситуации, когда такая операция оправдана и имеет смысл - но такая ситуация абсолютно всегда одноразовая, и есть составная часть административного обслуживания. А если подобная надобность возникла на уровне пользователя, в рабочем процессе - то это гарантия наличия серьёзной ошибки в проектировании БД.

Практически всё то же относится и к корректировке просто уникального поля. За исключением случая, когда выполняется каскадное изменение значения поля, которое в основной таблице получило значение NULL. То есть когда выполняемая операция по смыслу является не обновлением, а "мягким удалением" основной записи с каскадным удалением всех подчинённых. Правда, на вопрос, как отличить мягко каскадно-удалённые подчинённые записи от мягко явно-удалённых, и как определить, с какой основной записью была связана мягко удалённая подчинённая, не залезая в журнал или бэкап, ответа никто не даст. А получается, что даже в случае исключения всё делается через "универсальный интерфейс", то есть косяк в проектировании структуры имеется и в этом случае.

Резюмирую. Если каскадное обновление необходимо, оно скорее всего маскирует недостатки и ошибки проектирования. А плохо это или хорошо - прикрывать дырку костылём,- решайте сами.

Ну там не головоломки, а самые обычные задачки.

Да, сайты, где есть не только SQL-задачи, но и их решения, имеются. Тот же sql-ex, например.. Правда, не припомню таких, где бы предлагалось и объяснялось решение именно авторами сайта - решения (а практически любая задача имеет много альтернативных, порой радикально различающихся подходом, решений) можно найти либо в комментариях пользователей к задаче, либо на прилагаемом форуме.

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

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

UPDATE test
JOIN ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) new_id
       FROM test ) AS cte USING (id)
SET test.new_id = cte.new_id;

id - UNIQUE NOT NULL, либо вообще PRIMARY KEY. Поле либо выражение.

WITH cte AS (
  SELECT *,
         SUM((flag IS NULL) :: INT) OVER (PARTITION BY user_id ORDER BY product_id ASC) grp
  FROM test
  )
SELECT product_id, user_id, flag,
       DENSE_RANK() OVER (PARTITION BY user_id ORDER BY grp ASC) "rank"
FROM cte
ORDER BY 1,2;

fiddle

Модельное решение:

SELECT brand.b_id
FROM brand
NATURAL LEFT JOIN model 
NATURAL LEFT JOIN item
GROUP BY 1
HAVING NOT SUM(i_id IS NULL)

fiddle

Выгружаемые запросом данные - это именно данные. Сведения о структуре выходного набора - это метаданные. Получить одновременно их в одном запросе в общем не получится.

Возможное решение - UNION ALL двух запросов. Первый - получающий список полей таблицы из INFORMATION_SCHEMA.COLUMNS в виде CSV, второй - собственно показанный запрос. Поскольку UNION ALL не перемешивает выходные записи, получится вроде бы то, что нужно.

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

См. fiddle

Можно, конечно, использовать какую-нибудь дурь типа

WITH cte ( поля ) AS (
    -- твой запрос
    )
SELECT [ поля ] FROM cte
UNION ALL
SELECT NULL, ... , NULL WHERE NOT EXISTS (
    SELECT NULL FROM cte
    )

Но, право слово...

а что делать с реляционной бд

А ничего. НЕ хранить ежедневные итоги. Только приходы и уходы. И самая первая запись - только приход (начальный баланс), если начинаем не с пустой кассы. А сумму с накоплением считаем непосредственно в запросе, в тот момент, когда потребовался отчёт.

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

насколько оптимально такое решение?

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

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

Актуализация данных таблицы (точнее, её полный пересчёт) выполняется из триггерной процедуры на каждое событие изменения любой из используемых в запросе таблиц, если изменение связано с вашей системой (заодно можно выпилить оттуда не связанные с приложением записи). Поскольку работа по изменению указанных данных достаточно редкая по сравнению с запросами на получение данных, такая схема может дать определённый профит. Однако, поскольку процесс предусматривает полную очистку таблицы и заполнение её с нуля, следует избегать интерференции со стороны двух конкурентных процессов изменения. Для полной гарантии любые изменения следует проводить в изолирующей транзакции.

Хотя как по мне, следует отделить систему безопасности Постгресса от авторизации в приложении. Мнение по данному вопросу от Everything_is_bad в комментарии - это не сарказм, а весьма правильное замечание.

Ну вообще-то хранение изображений в полях базы данных считается антипаттерном... а по сути - это вообще худшее из возможных решений.

Наилучшее решение - файлы хранятся в FileTables либо FILESTREAM, а в таблице лежат ссылки на них. Что приятно - при этом не надо распыляться с безопасностью, за ней следит SQL Server.

Сравнение параметров для хранения больших двоичных...

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

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

SELECT *
FROM Author
WHERE NOT EXISTS (
    SELECT NULL
    FROM Book
    WHERE Author.Authorld = Book.Authorld
      AND Book.PublishDateTime >= CURRENT_DATE - INTERVAL 2 YEAR
    )

SELECT table1.*, 
       EXISTS ( SELECT NULL
                FROM table2
                WHERE table2.table1_id = table1.id
                   AND table2.date = @needed_date
                ) AS row_exists
FROM table1

Если точная дата внутри месяца неизвестна, то

WHERE table2.date BETWEEN @needed_month_1st_day AND @needed_month_last_day