Как получить данные и обновить записи в таблицах-источниках?

Question

[Дмитрий]

Завязка:
У меня есть две таблицы с исходными данными. В каждой таблице есть признак того, что строка была обработана (после записи - false).
Мне нужно брать из них данные, как-то обрабатывать, а после - записывать в таблицу (таблицы, но тут не особо важно - пусть будет одна) с рассчитанными данными, возвращать результат клиенту, а так же изменять признак обработки строк в первых двух таблицах (естественно, нужно откатываться при проблемах).
Если не брать в расчёт обновление и добавление данных в таблицы - будет как-то так:

with t1 as (
--первая таблица с данными
	select * from table1
	where "IsProcessed"=false;
),t2 as (
--вторая таблица с данными
	select * from table2;
	where "IsProcessed"=false;
)
--здесь происходит обработка данных (джойны и другое интересное)
, result as (
	--здесь уже результат
)
select * from result;

суть:
Насколько плохо будет запихнуть апдейты и инсерты в cte? Как я понял из доков - так делать можно:

with t1 as (
--первая таблица с данными
	select * from table1
	where "IsProcessed"=false;
),t2 as (
--вторая таблица с данными
	select * from table2;
	where "IsProcessed"=false;
)
--здесь происходит обработка данных (джойны и другое интересное)
, result as (
	--здесь уже результат
),upd1 as(
	update table1
		set "IsProcessed"=true
	from
		t1
	where t1.id=table1.id
),upd2 as(
	update table2
		set "IsProcessed"=true
	from
		t2
	where t2.id=table2.id
),ins1 as(
	insert into result_table
		select from result 
)
select * from result

Если это совсем плохо - как сделать лучше?

upd: дополнил последний блок кода, чтобы избежать неоднозначностей

Comments

[idShura]

Все плохо. Update в подзапросе это сильно! Раздели на отдельные операции (select, update) и оберни в транзакцию

[Дмитрий]

idShura, Конечно, заманчиво, когда фактически - у меня есть один запрос, который сразу и данные подготовит, и апдейты нужные сделает, и не надо никуда данные из cte перекладывать.
Тут больше даже вопрос о том, чем это плохо (не настаиваю, что так делать хорошо, или что мне именно такое нужно, просто интересны реальные проблемы с таким решением)

[Akina]

Тут больше даже вопрос о том, чем это плохо

С чего бы плохо-то? Тут разве что читабельность может пострадать, особенно если запрос получится объёмный да многосоставный. Но если хорошо документировать и комментировать запрос да использовать вменяемые алиасы, так и вовсе тут ничего плохого.

Answer 1

[Akina]

Насколько плохо будет запихнуть апдейты и инсерты в cte?

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

Хотя именно для показанного шаблона (один UPDATE, и нужно получить обновлённые записи) я не понимаю, почему просто не сделать финальный апдейт внешним запросом с использованием кляузы RETURNING. Ваш финальный select * from result смотрится совершенно бессмысленной нашлёпкой.

Comments

[Дмитрий]

Я не учёл, что код отвлечёт от текста. Дополнил последнюю вставку кода
На самом деле там будет два update (на каждую таблицу с данными) + один insert + возврат данных, но не совсем в том виде, в котором записываются в таблицу с результатами (тот самый select * from result подразумевает нечто большее)

[Akina]

Дмитрий, ну тогда будет один комплексный запрос с несколькими CTE. Нормальное вменяемое решение. Я не зря ну очень заострил внимание на том, что речь строго о показанном шаблоне.
И по скорости почти наверняка выиграешь солидно.

[Дмитрий]

Akina, в погоне за скоростью я к этому решению внезапнои пришёл
Спасибо!